topic Re: re-orienting observations/column values in SAS Programming

re-orienting observations/column values

TEJ — Fri, 27 Nov 2015 18:55:50 GMT

Hi,

I am working on .csv file- importing to SAS for analysis. it's a lab data with columns like patient ID, dob, tests performed, results etc. There's varying # of observations for same ID- each repeatition being the "ID" for each test performed for this patient, sth like this-

ID DOB TEST RESULT

001 xx-xx-xxxx test result

002 xx-xx-xxxx test result

002 xx-xx-xxxx test2 result2

002 xx-xx-xxxx test3 result3

003 xx-xx-xxxx test result

003 xx-xx-xxxx test2 result2

I want to create a dataset such that for each ID, i have columns- test1, test2, test3 etc. (and similarly, result result1 result2 etc.) and have all tests, results for this ID in 1 row.

How can I accomplish this?

I do not necessarily need same IDs in my final dataset (not sure if this makes any difference though)

Thank you,

Re: re-orienting observations/column values

PGStats — Fri, 27 Nov 2015 19:15:15 GMT

You want a simple transposition of your data, from long to wide. Experienced SAS users will tell you that the wide format is less useful than the long for most purposes, except reporting. Here is how to do it.

proc transpose data=myData out=myTable;
by id dob notsorted;
id test;
var result;
run;

Re: re-orienting observations/column values

Reeza — Fri, 27 Nov 2015 20:09:20 GMT

Because you have two variables, I think you need either 2 proc transposes and a merge Or a datastep. Or you can use a macro designed by some very savy users. Links are below.

Using a Data Step
http://www.ats.ucla.edu/stat/sas/modules/longtowide_data.htm

Using Proc Transpose
http://www.ats.ucla.edu/stat/sas/modules/ltow_transpose.htm

Macro
http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

Re: re-orienting observations/column values

FreelanceReinh — Fri, 27 Nov 2015 21:01:49 GMT

Another elegant way to transpose two or more variables in a BY-group, just with a data step, can be found in the award-winning paper "Practical Uses of the DOW Loop in Pharmaceutical Programming" (see Example 1 there).

However, I doubt it would be useful for you to go beyond PG's suggestion and transpose both TEST and RESULT.