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

cartergay — Thu, 04 Jan 2024 19:48:34 GMT

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

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

PaigeMiller — Thu, 04 Jan 2024 20:05:10 GMT

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.

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

cartergay — Thu, 04 Jan 2024 20:09:44 GMT

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

topic Re: Transpose Table With Columns for Each Month to One Row Per Month in SAS Programming

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

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

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