Retain Statement Data Spread over multiple lines SAS

Question

Im having trouble with a dataset. There is a free form character variable spread over multiple lines. I want to put this variable into new variables so each observation is on its own line

Heres an example dataset

data have;
   input ob_ref $ chardata $ linenum 3.;
   datalines;
321A    MyName  1
321A    John    2
431A    MyName  1
431A    NotJohn 2
431A    ItsFred 3
511A    Geoff   1
754A    Cam 1
;

There is an ob_ref for each observation and I have a linenum variable indicating how many lines the observations data is spread over. I need a new dataset with one observation per line and the character variable spread over multiple variables. The maximum linenum is 56

Any help is greatly appreciated

For clarity, please could you add the expected output from your example — Longfish

DWal DWal · Accepted Answer · 2015-05-12T13:34:38

I think you're looking for something like this? Create an array of your character variables, then use the linenum variable to assign values to the correct character variables. Output on the last row for a given ob_ref.

data want(keep=ob_ref char1-char56);
  array _c $ char1-char56;
  do until (last.ob_ref or eof);
    set have end=eof;
    by ob_ref;
    _c(linenum)=chardata;
  end;
  output;
run;

Retain Statement Data Spread over multiple lines SAS

1 Answers