Creating multiple observations from single observation in SAS

Published on:
written bySubhro
SAS PROGRAMS

In the previous post, we have 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 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;

Output:

 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 <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 have to 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.

Leave a Reply

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

Share via
Copy link
Powered by Social Snap