Help using Base SAS procedures

Opposite of proc transpose

Reply
Not applicable
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,852

Re: Opposite of proc transpose

[ Edited ]
Posted in reply to deleted_user

PROC TRANPOSE....

 

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;

 


               New     New
 Obs    Id    Var1     Var2

  1      1    Prod1     A
  2      1    Prod2     B
  3      1    Prod3     C
  4      2    Prod1     D
  5      2    Prod2     E
  6      2    Prod3     F


 
Modified to NAME new variables created by TRASNSPOSE
Message was edited by: data _null_;

 

SAS Super FREQ
Posts: 9,371

Re: Opposite of proc transpose

Posted in reply to data_null__
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
  • 5076 views
  • 3 likes
  • 3 in conversation