How to efficiently create duplicate copies in SAS

0
votes

Suppose I have a dataset as follows:

ID Col1 Col2 Col3 Value
1   2   5     6    100
2   32  42         999

and I want to create a data set as follows:

  ID Col Value
   1  2  100
   1  5  100
   1  6  100
   2  32 999
   2  42 999

what would be the best way to go about doing this? Note, I have a missing value for Col3 in my original dataset, so I would only want to create new rows where there are no missing values in the columns.

2
sas

2 Answers

2
votes

Simply use PROC TRANSPOSE to get the desired output. ID and Value column can be used with BY statement since that defines the group for exapanding value variable. 

proc transpose data=input_data out=output_data(where=(col1 ne .));
    by id value;
    var col1-col3; 
run;
0
votes

Or you could use arrays...

data want ;
  set have ;
  array c{*} col1-col3 ; 
  do i = 1 to dim(c) ;
    if not missing(c{i}) then do ;
      col = c{i} ;
      output ;
    end ;
  end ;
  drop col1-col3 i ;
run ;