topic Re: transform the data in SAS Procedures

transform the data

Pavani2 — Fri, 29 Sep 2023 16:50:13 GMT

i need the output as below,can anyone help me

id profit1 profit2 profit3 profit4 revenue1 revenue2 revenue3 revenue4 1 2 5 7 9 10 20 30 40 2 3 8 . . 3 8 .

Data I have

Data have; input id serial Revenue profit; cards; 1 1 10 2 1 2 20 5 1 3 30 7 1 4 40 9 2 1 5 3 2 2 10 8 2 3 . . 2 4 . . ; Run; proc print; run;

Re: transform the data

Tom — Fri, 29 Sep 2023 16:55:30 GMT

Seems like a simple application of using ARRAYs.

Let's rename the original variables so we can use revenue and profit as the names of the arrays.

data want;
  do until(last.id);
    set have(rename=(profit=_profit revenue=_revenue));
    by id;
    array profit [4];
    array revenue [4];
    profit[serial] = _profit;
    revenue[serial] = _revenue;
  end;
  drop serial _profit _revenue;
run;

Result

Obs    id    profit1    profit2    profit3    profit4    revenue1    revenue2    revenue3    revenue4

 1      1       2          5          7          9          10          20          30          40
 2      2       3          8          .          .           5          10           .           .

Re: transform the data

PaigeMiller — Fri, 29 Sep 2023 18:29:40 GMT

Usually there is little benefit to transposing long to wide. If you want to create a report (as opposed to a SAS data set) in this wide format that you want, leave the data as it is in the long arrangement, and use PROC REPORT.

proc report data=have;
    columns id profit,(" " serial) revenue,(" " serial) ;
    define id/group 'ID';
    define serial/across " ";
    define revenue/sum 'Revenue';
    define profit/sum 'Profit';
run;

There are plenty of formatting capabilites to make the above PROC REPORT output look nicer if needed.