Transpose Table With Columns for Each Month to One Row Per Month

[cartergay]

I have a table with the following columns:

• Member_ID
• Contract_Jan, ..., Contract_Dec
• PBP_Jan, ..., PBP_Dec
• Cost_Sharing_Jan, ..., Cost_Sharing_Dec

I would like to transpose this table into the following format:

• Member_ID
• Month
• Contract
• PBP
• Cost_Sharing

I want to avoid using PROC SQL union all since the data I am querying is extremely large

[PaigeMiller · Accepted solution]

Good idea to avoid PROC SQL here. I don't even know if SQL can do this transpose, but I'm sure I don't want to try to figure out how to program it in SQL.

From now on, please provide a portion of your data following these examples and isntructions. Here is fake data converted to the requested format.

data have;
    input member_id contract_jan contract_feb contract_mar contract_apr pbp_jan pbp_feb pbp_mar pbp_apr cost_sharing_jan cost_sharing_feb
        cost_sharing_mar cost_sharing_apr;
    cards;
123 1 2 3 4 5 6 7 8 9 10 11 12
143 13 14 15 16 17 18 19 20 21 22 23 24
431 14 24 34 44 54 64 74 84 94 104 114 12
999 135 145 155 165 175 185 195 205 215 225 235 24
;

data want;
    set have;
    array c contract:;
    array p pbp:;
    array cs cost_sharing:;
    do i=1 to dim(c);
        _contract=c(i);
        _pbp=p(i);
        _cost_sharing=cs(i);
        monthname=scan(vname(c(i)),2,'_');
        date=input(cats('01',monthname,'2019'),date9.);
        output;
    end;
    drop i contract: pbp: cost_sharing: monthname;
    format date monyy7.;
run;

In addition, it would be wise not to work with months as character strings; a better way to handle this is to turn these month character strings into actual valid SAS date values. I have done this in the code above, stealing a method from @Patrick here.

[cartergay]

Thanks this is perfect. I will follow that post from now on