SAS: Append Values From Multiple Columns Into One Column (Reverse Transpose)

by

You can append values from the multiple columns into one column in SAS. It’s also called reverse transposing the dataset.

You are appending values by merging columns into one column against one unique variable.

The following sample dataset will be used to demonstrate how to append values from multiple columns into one column (reverse transposing dataset).

/* create dataset */
data haveTable;
	input id code1 $ code2 $ code3 $;
	datalines;
1 ABC EFG XYZ
2 ABC . .
3 . ABC XYZ
;
run;

/* view dataset */
proc print data=haveTable;
run;
SAS Append Values Of Multiple Columns Into One Column

Append Values From Multiple Columns Into One Column

If you observe the above sample dataset , ID is a unique variable. There are multiple codes against each unique ID. Now you want to append all those different codes into a single column named “overallCodes” but retaining the ID values.

The requirement is kind of reverse transposing the dataset. It can be done using ARRAYS in SAS. 

A SAS array is a set of variables of the same type that you want to perform the same operation on. The variables in an array are called elements and can be accessed based on their position (also called as an index). 

You use the name of the array to reference the set of variables. Arrays are useful to create new variables and carry out repetitive tasks.

In this example your repetitive task is to collect all the codes against each ID value and put them in a new variable.

/* SAS: Append Values Of Multiple Columns Into One Column */
data wantTable (where=(Overall_codes ne ''));
	set haveTable;
	array cvars {*} _character_; /* define char array */

	do i=1 to dim(cvars);
		Overall_codes=cvars{i}; /* create and assign combined values to a new variable */
		output;
	end;
	drop i code1-code3; /* drop variables */
run;

/* view dataset */
proc print data=wantTable;
run;
SAS create and assign combined values to a new variable

ADDITIONAL RESOURCES: