Hi SAS Community:
I have this sample data:
Period Product Var1 Var2 Var3 201701 R1 10 10 0 201701 R1 20 10 10 201702 R1 30 10 20 201702 T2 40 20 20 201702 T1 50 30 20 201703 T1 60 10 50 ... ... ... ... ... 201801 R1 20 20 0
And I need to pivot it to a yearly report like this:
Product 201701Var1 201701Var2 201701Var3 201702Var1 ... 201801Var1 201801Var2 201801Var3 R1 30 20 10 30 ... 20 20 0 T1 0 0 0 50 ... 0 0 0 T2 0 0 0 40 ... 0 0 0
I'm currently using proc transpose to pivot it, but i can't avoid repeating the product 3 times for each Variable I have
proc TRANSPOSE data=work.sample out=pivot;
by product;
VAR Var1 Var 2 Var 3;
ID Period;
proc REPORT data=pivot ls=120 nowd;
run;
Transform your period variable to an actual SAS date variable with an appropriate format wouldn't hurt.
If you want a report use a report procedure such as proc tabulate:
proc tabulate data=have;
class period;
class product;
var var1 var2 var3;
table product='',
period='' * (var1 var2 var3)*sum=''*f=best10.
/ misstext='0' box='Product'
;
run;
the ='' (two single quotes) are suppressing column/row headers.
Transform your period variable to an actual SAS date variable with an appropriate format wouldn't hurt.
If you want a report use a report procedure such as proc tabulate:
proc tabulate data=have;
class period;
class product;
var var1 var2 var3;
table product='',
period='' * (var1 var2 var3)*sum=''*f=best10.
/ misstext='0' box='Product'
;
run;
the ='' (two single quotes) are suppressing column/row headers.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.