Creating multiple observations from single observation in SAS

0
14

On the previous post, I have discussed on Creating a single record from multiple records in SAS. What if you want to go the other approach, that is creating a data set with multiple observations from single observation per ID?

The process is simple and I have taken the output from the previous example as an input for this example. You can download the example dataset and programs from the link at the end of this article.

So, first, sort the input data set by ID.

Read: The Mystery of Proc Sort Options

proc sort data=single_to_multiple;
by id Visits;
run;
data multiple_to_single;
set single_to_multiple;
by id Visits;
array D{3};
retain D1-D3;
if first.id then call missing(of D1-D3);
D{Visits} = Diagnosis;
if last.id then output;
keep id D1-D3;
run;

 multiple observations from single observation

Next, you have to set up an array to hold the three D values and retain these three variables. You have to retain these three variables because they don’t come from a SAS data set and are, by default, set equal to a missing value for each iteration of the DATA step.

Read: Using RETAIN in SAS to remember values

Read: Essential guide of using Arrays in SAS

The RETAIN statement prevents this from taking place. Next, once you start processing the first visit for each ID, you set the three values of D to missing.

If you don’t do that, an ID with fewer than three visits could wind up with a diagnosis from the previous topic. The CALL MISSING routine can set any number of numeric and/or character values to missing at one time.

As with most of the SAS functions and CALL routines, if you happen to use a variable list in the form Var1–Varn, you have to precede the variable list with the key phrase OF.

Next, you have to assign the value of Diagnosis to the appropriate D variable (D1 if Visit=1, D2 if Visit=2, and D3 if Visit=3).

Finally, if you’re processing the last visit for an ID, you have to provide the output with a single observation keeping the variables ID and D1–D3.

Download Exercise Files

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.