Hello everyone,
I have data like this:
month | type | total_2021 | delta |
1 | ip | $13,523,323 | -14% |
2 | ip | $12,969,525 | -16% |
3 | ip | $13,678,954 | -22% |
1 | ipop | $68,414,734 | -7% |
2 | ipop | $62,715,246 | -7% |
3 | ipop | $68,270,752 | -2% |
1 | op | $54,891,411 | -5% |
2 | op | $49,745,721 | -4% |
3 | op | $54,591,797 | 5% |
What I would like to do, but am not sure how is to get the data like this:
type | 1 | 1_delta | 2 | 2_delta | 3 | 3_delta |
ip | $13,523,323 | -14% | $12,969,525 | -16% | $13,678,954 | -22% |
ipop | $68,414,734 | -7% | $62,715,246 | -7% | $68,270,752 | -2% |
op | $54,891,411 | -5% | $49,745,721 | -4% | $54,591,797 | 5% |
Any assistance would be great. Below please find the code to create the dataset.
data have;
input month type $ total_2021 delta;
format delta percentn10.0 total_2021 dollar12.0;
datalines;
1 ip 13523323 -0.14153
2 ip 12969525 -0.15819
3 ip 13678954 -0.21653
1 ipop 68414734 -0.06826
2 ipop 62715246 -0.06579
3 ipop 68270752 -0.01973
1 op 54891411 -0.04825
2 op 49745721 -0.03827
3 op 54591797 0.04611
;
run;
Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/
https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/
Both of these cover your use case exactly.
Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/
https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/
Both of these cover your use case exactly.
The first step is to create groups by type:
proc sort data=have;
by type;
run;
data h;
set have;
group + 1;
by type;
if first.type then group = 1;
run;
Concatenate groups with either "Total" or "Delta" for transposed column names. I would transpose totals and deltas separately then merge.
e.g., name = catx("Total", "_", group);
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: