topic Re: Data Transpose/Pivot in SAS Programming

Data Transpose/Pivot

Tony5 — Tue, 23 Jan 2018 23:43:35 GMT

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:

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.

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

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

Regards,

Tony

Re: Data Transpose/Pivot

Reeza — Tue, 23 Jan 2018 23:52:49 GMT

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:

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.

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

Re: Data Transpose/Pivot

ballardw — Wed, 24 Jan 2018 00:27:14 GMT

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

Re: Data Transpose/Pivot

Tony5 — Wed, 24 Jan 2018 00:55:44 GMT

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.

Re: Data Transpose/Pivot

Tony5 — Wed, 24 Jan 2018 00:58:53 GMT

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!

Re: Data Transpose/Pivot

Tom — Wed, 24 Jan 2018 01:14:51 GMT

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

Re: Data Transpose/Pivot

Tony5 — Wed, 24 Jan 2018 01:58:47 GMT

Super helpful. Thanks Tom!