EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer
Physics
C.S.


Tips to Trun around & Transpose data nicely
Cool application for Proc Transpose and array statement

Question: When we try to transpose our deta from horizontal to vertical, or vice versa, we usually have some difficult situation like the following: there are some missing values in the original table, after applying the transpose procedure, the order of the columns is changed.

When we apply proc transpose procedure to transpose the data via By statement, the order of the transposed columns is dependent on the first by "value" in the dataset, if the first by "value" has id variable ordered, then it will show up in that order from left to right in the transposed dataset.


        /****************************************************/
        data sbp;
        input subject $ visit sbp;
        datalines;
        101 1 160
        101 3 140
        101 4 130
        101 5 120
        202 1 141
        202 2 161
        202 4 171
        202 5 181
        ; run;

        **** SORT SBP VALUES BY SUBJECT.;
        proc sort data = sbp;
        by subject; run;

        **** Apply proc transpose to turn data around;
        proc transpose data=sbp out=sbp_flat1(drop=_name_) prefix=visit_;
        by subject; id visit; var sbp; run;

We propose the following several ways to resolve the issue.

        Method (1)(recommended): apply retain statement to re-order the position.
        /****************************************************/
        data sbp_flat2;
        retain subject _name_ visit_1-visit_5;
        set sbp_flat1;         run;

        You can also use a macro variable to get all the variables in order.
        /****************************************************/
        proc contents data=datain out=allvars; run;

        proc sql;
        select _name_ into: all_vars
        separated by ' '
        from allvars; quit;


        Method (2) (smart application for retain, not that easy to understand):
        /****************************************************/
        **** TRANSPOSE THE NORMALIZED SBP VALUES TO A FLAT STRUCTURE.;
        data sbpflat;
        set sbp;
        by subject;
        keep subject visit1-visit5;
        retain visit1-visit5;

        **** DEFINE ARRAY TO HOLD SBP VALUES FOR 5 VISITS.;
        array sbps {5} visit1-visit5;

        **** AT FIRST SUBJECT, INITIALIZE ARRAY TO MISSING.;
        if first.subject then
        do i = 1 to 5;
        sbps{i} = .;
        end;

        *** Load sbp value into proper slot in the array.
        sbps{visit} = sbp;

        **** KEEP THE LAST OBSERVATION PER SUBJECT WITH 5 SBPS.;
        if last.subject;
        run;


Some highly-recommended data visulization books
       

Related links:

Continue to next: How to extend weekly data into daily?   SAS tutorial home
Back to: Change SAS Table view to see columns names, not labels   Statistics tutorial home