BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Rcuevas
Fluorite | Level 6

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

1 REPLY 1
ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 3244 views
  • 0 likes
  • 2 in conversation