So I have 10 million rows in a dataset and this is what data looks like (on left). consists of decile ,a category and profits.
Now I need to create something on my right. Decile as rows, category as column and mean of profits as the values in the table. So for example - all the values that are in decile 1 and category a, their mean profit is 300 dollars.
Methinks this is what proc TRANSPOSE is designed for:
data have ;
input decile category :$1.profit ;
cards ;
1 a 300
2 h 500
4 t 670
6 u 540
8 q 109
1 q 409
9 r 870
;
run ;
proc sort data = have out = haves ;
by decile ;
run ;
proc transpose data = haves out = want (drop = _:) ;
by decile ;
id category ;
var profit ;
run ;
Kind regards
Paul D.
Or possibly as simple as:
proc tabulate data=have; class decile category; var profit; table decile=' ', category*profit=''*mean='' /misstext=' ' box=decile ; run;
Had thought of, too (love proc TAB). If the OP needs a report rather than a data set, it's picture perfect.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.