Creating multiple observations from single observation in SAS

In the previous post, we discussed 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 a single observation per ID?

The process is simple, and we have taken the output from the previous example as 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;

Output:

 multiple observations from single observation

Next, you must 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 to 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 result in a diagnosis from the previous topic. The <a href="https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=p1iq436yh8838rn1ud38om45n99k.htm&docsetVersion=9.4&locale=en">CALL MISSING</a> 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 must provide the output with a single observation keeping the variables ID and D1–D3.

Download Exercise Files

Every week we'll send you SAS tips and in-depth tutorials

JOIN OUR COMMUNITY OF SAS Programmers!

Subhro Kar is an Analyst with over five years of experience. As a programmer specializing in SAS (Statistical Analysis System), Subhro also offers tutorials and guides on how to approach the coding language. His website, 9to5sas, offers students and new programmers useful easy-to-grasp resources to help them understand the fundamentals of SAS. Through this website, he shares his passion for programming while giving back to up-and-coming programmers in the field. Subhro’s mission is to offer quality tips, tricks, and lessons that give SAS beginners the skills they need to succeed.