CODEWITHSUNDEEP
sas
lord12
lord12

Reputation: 2917

How to efficiently create duplicate copies in SAS

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.

Upvotes: 0

Views: 233

Answers (2)

Chris J
Chris J

Reputation: 7769

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 ;

Upvotes: 0

sushil
sushil

Reputation: 1576

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;

Upvotes: 2

Related Questions