Data Transpose/Pivot

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Data Transpose/Pivot

Hi again,

 

1st of all, below is the SAS code for the data:

data have;
	input zip $ item $ box1 box2 box3 percent;
	format percent percent. ;

	datalines;
		00501 22 1 1 0 0.1
		00501 42 2 0 0 0.3
		00501 99 1 1 6 0.6
		00101 22 2 10 30 0.28
		00101 23 48 24 60 0.45
		00101 31 40 20 50 1
		00101 42 56 36 70 0.42
		;
run;

proc print;
run;

Which will give you this:

SAS Transpose 1.png

 

I'd like to ask for the simplest solution to be able to go from that table, to a semi-transposed (or technically, a pivoted) dataset.

SAS Transpose 2.png

 

The item codes will be constant. Below are all the possible item codes if it matters:

22
23
31
42
52
62
72
82
99

Any help pointing me to the right direction will be very much appreciated.

 

Regards,

Tony

 


Accepted Solutions
Solution
‎01-23-2018 07:51 PM
Super User
Posts: 22,874

Re: Data Transpose/Pivot

PROC TRANSPOSE will transform the data, or PROC TABULATE works if you just want a printed report. 

 

proc sort data=have;
by zip item;
run;

proc transpose data=have out=want prefix=item;
by zip ;
var percent;
id item;
idlabel item;
run;

proc print data=want;run;

Tony5 wrote:

Hi again,

 

1st of all, below is the SAS code for the data:

data have;
	input zip $ item $ box1 box2 box3 percent;
	format percent percent. ;

	datalines;
		00501 22 1 1 0 0.1
		00501 42 2 0 0 0.3
		00501 99 1 1 6 0.6
		00101 22 2 10 30 0.28
		00101 23 48 24 60 0.45
		00101 31 40 20 50 1
		00101 42 56 36 70 0.42
		;
run;

proc print;
run;

Which will give you this:

SAS Transpose 1.png

 

I'd like to ask for the simplest solution to be able to go from that table, to a semi-transposed (or technically, a pivoted) dataset.

SAS Transpose 2.png

 

The item codes will be constant. Below are all the possible item codes if it matters:

22
23
31
42
52
62
72
82
99

Any help pointing me to the right direction will be very much appreciated.

 

Regards,

Tony

 


 

View solution in original post


All Replies
Solution
‎01-23-2018 07:51 PM
Super User
Posts: 22,874

Re: Data Transpose/Pivot

PROC TRANSPOSE will transform the data, or PROC TABULATE works if you just want a printed report. 

 

proc sort data=have;
by zip item;
run;

proc transpose data=have out=want prefix=item;
by zip ;
var percent;
id item;
idlabel item;
run;

proc print data=want;run;

Tony5 wrote:

Hi again,

 

1st of all, below is the SAS code for the data:

data have;
	input zip $ item $ box1 box2 box3 percent;
	format percent percent. ;

	datalines;
		00501 22 1 1 0 0.1
		00501 42 2 0 0 0.3
		00501 99 1 1 6 0.6
		00101 22 2 10 30 0.28
		00101 23 48 24 60 0.45
		00101 31 40 20 50 1
		00101 42 56 36 70 0.42
		;
run;

proc print;
run;

Which will give you this:

SAS Transpose 1.png

 

I'd like to ask for the simplest solution to be able to go from that table, to a semi-transposed (or technically, a pivoted) dataset.

SAS Transpose 2.png

 

The item codes will be constant. Below are all the possible item codes if it matters:

22
23
31
42
52
62
72
82
99

Any help pointing me to the right direction will be very much appreciated.

 

Regards,

Tony

 


 

Occasional Contributor
Posts: 9

Re: Data Transpose/Pivot

Thank you so much for the solution Reeza. It worked beautifully! And your assumption is on point, I do need the transposed table as a dataset (as opposed to a printout).

 

Will research more what TRANSPOSE does. For starters, it seems that PREFIX, ID, and IDLABEL would be the same thing most of the time.

Super User
Super User
Posts: 7,860

Re: Data Transpose/Pivot


Tony5 wrote:

Thank you so much for the solution Reeza. It worked beautifully! And your assumption is on point, I do need the transposed table as a dataset (as opposed to a printout).

 

Will research more what TRANSPOSE does. For starters, it seems that PREFIX, ID, and IDLABEL would be the same thing most of the time.


If you don't use the ID statement to tell PROC TRANSPOSE how to name the variables they will just be named COL1,COL2,...

If you do give it an ID statement and try to use a numeric variable for generating the names you could have a problem since you can't make a variable with a number for a name like in your original request.  If you don't specify a PREFIX then SAS will use _ as a prefix to turn the numbers into valid variable names. 

The IDLABEL statement is in case you have a variable that want to use as source for the LABEL of the new variables. 

Occasional Contributor
Posts: 9

Re: Data Transpose/Pivot

Super helpful. Thanks Tom!
Super User
Posts: 13,084

Re: Data Transpose/Pivot


Tony5 wrote:

Hi again,

 

 

I'd like to ask for the simplest solution to be able to go from that table, to a semi-transposed (or technically, a pivoted) dataset.

 

 


Data sets don't really "pivot" that is very much a spreadsheet term.

What role to the BOX values in calculating your results? I am not sure what the denominators or numerators are for any of those result cells in your "want".

Occasional Contributor
Posts: 9

Re: Data Transpose/Pivot

Thanks for the response ballardw.

 

I guess my Excel roots are showing up. Hehe.

To answer your question, I actually just need the last column to show up (the "percent" variable). All box values will be dropped.

 

In any case though, Reeza was able to provide a solution a few minutes ago. Still, I appreciate your support. Have a great day ahead!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 153 views
  • 3 likes
  • 4 in conversation