Creating a single record from multiple records in SAS

Creating a single record from multiple records in SAS is to transform a data set with one observation per subject and convert it to a data set with many observations per subject.

Why is this required?

It is easier to analyse when all the information per subject is in a single observation, for example, generating a frequency distribution of the data.

In this post, I will demonstrate how to restructure your data sets using the DATA step.

Creating a single record from multiple records in SAS using a Data Step

This example uses a small data set -raw_data where each subject ID has one to three diagnosis codes (D1–D3).

raw data

Note that some subjects’ ids have three diagnosis codes, some two, and one (id 4) only one.  How do you obtain a frequency  ID 1 with code 55 as the 1st diagnosis and ID 2 with this code listed as d3.?

It is difficult to create a frequency distribution of this data.

To overcome this, you can create a single Observation from multiple records as below.

data Single_to_Multiple;
set raw_data;
array d{3};
do visits = 1 to 3;
Diagnosis = d{visits};
output;
end;
keep id visits Diagnosis;
run;
Single Observation from Multiple Records

The D array has three elements: D1, D2, and D3. If you leave off the variable list, the variable names default to the array name with the digits 1 to n appended to the end.

Read the Essential guide for using Arrays in SAS.

The DO loop starts with a Visit set equal to 1. A new variable, Diagnosis, is set equal to the value of the array element D{1}, which is the same as the variable D1, which is equal to 55.

Now, to obtain the frequency of the Diagnosis code, you can use the PROC FREQ procedure as below.

proc freq data=Single_to_Multiple;
tables Diagnosis/nocum nopercent;
run;
Frequency Distribution
Frequency Distribution

From the Proc Freq output, you can see that the count of diagnoses has been generated along with the count of missing values in the dataset.

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.