Help using Base SAS procedures

Opposite of proc transpose

Reply
N/A
Posts: 0

Opposite of proc transpose

Can anyone tell me the best way of doing the "opposite of proc transpose"?

I have the following data set:

Id Prod1 Prod2 Prod3
1 A B C
2 D E F

And I want to turn into this:

Id NewColumnName NewColumnName2
1 Prod1 A
1 Prod2 B
1 Prod3 C
2 Prod1 D
2 Prod2 E
3 Prod3 F

Anybody know the best way to do this?

Thanks in advance!!
Respected Advisor
Posts: 3,777

Re: Opposite of proc transpose

PROC TRANPOSE....

[pre]
data have;
input Id (Prod1 Prod2 Prod3)($);
cards;
1 A B C
2 D E F
;;;;
run;
proc transpose
name = NewVar1
data = have
out = need(rename=(col1=NewVar2))
;
by id;
var prod:;
run;
proc print;
run;
[/pre] Modified to NAME new variables created by TRASNSPOSE


Message was edited by: data _null_;
SAS Super FREQ
Posts: 8,740

Re: Opposite of proc transpose

Hi:
And, to give you even MORE options ... (options are good), you can do the "untranspose" in a DATA step program without ever using PROC TRANSPOSE. Although, it is a favorite of mine.

cynthia

[pre]
data origdata
newdata(keep=ID newcol newcol2);
input Id (Prod1 Prod2 Prod3)($);
output origdata;

** Method1: Hard code values and create data when you;
** read in the "raw" data file.;
** This method might work better if new var names;
** were NOT sequentially numbered.;
newcol = 'Prod1';
newcol2 = prod1;
output newdata;
newcol = 'Prod2';
newcol2 = prod2;
output newdata;
newcol='Prod3';
newcol2 = prod3;
output newdata;
return;
datalines;
1 A B C
2 D E F
3 G H I
4 J K L
5 M N O
6 P Q R
;
run;

ods listing;
proc print data=newdata;
title '1) Without transpose, when read in data';
run;


** Method 2: use VVALUEX in a do loop. Could be done when;
** you read in the data. It just depends on whether;
** input variables are actually numbered.;
data newdata2(keep=id newcol newcol2);
length id 8 newcol $5 newcol2 $1;
set origdata;
do i = 1 to 3 by 1;
newcol = 'Prod'||put(i,1.0);
newcol2 = vvaluex(newcol);
output newdata2;
end;
run;

proc print data=newdata2;
title '2) Using SET, DATA step and do loop';
run;

[/pre]
Ask a Question
Discussion stats
  • 2 replies
  • 3416 views
  • 2 likes
  • 3 in conversation