Pivoting using SAS, build more columns

[Rcuevas]

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;

[ballardw · Accepted solution]

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.