topic Re: Opposite of proc transpose in SAS Procedures

Opposite of proc transpose

deleted_user — Wed, 11 Feb 2009 14:41:32 GMT

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!!

Re: Opposite of proc transpose

data_null__ — Fri, 01 Jun 2018 11:21:50 GMT

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_;

Re: Opposite of proc transpose

Cynthia_sas — Wed, 11 Feb 2009 23:52:13 GMT

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]