Creating a single record from multiple records in SAS2 min read

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).

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

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;
run;

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.