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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Reeza
Super User

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

 


 

Tony5
Fluorite | Level 6

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.

Tom
Super User Tom
Super User

@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. 

Tony5
Fluorite | Level 6
Super helpful. Thanks Tom!
ballardw
Super User

@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".

Tony5
Fluorite | Level 6

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!

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
  • 6 replies
  • 12497 views
  • 3 likes
  • 4 in conversation