Single Observation from Multiple Records

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};
keep id visits Diagnosis;
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;
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



Subhro provides valuable and informative content on SAS, offering a comprehensive understanding of SAS concepts. We have been creating SAS tutorials since 2019, and 9to5sas has become one of the leading free SAS resources available on the internet.

Leave a Reply

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