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

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

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