Creating a single record from multiple records in SAS2 min read

Like Tweet Pin it Share Share Email

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

Why is this required?

It is easier to perform analysis 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 from 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 had code 55 as the 1st diagnosis and ID 2 has this same 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: Essential guide for using Arrays in SAS.

The DO loop starts with 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 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 diagnosis has been generated along with the count of missing values in the dataset.

Comments (0)

Leave a Reply

Your email address will not be published. Required fields are marked *

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