BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TEJ
Calcite | Level 5 TEJ
Calcite | Level 5

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,

T

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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

View solution in original post

3 REPLIES 3
PGStats
Opal | Level 21

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;

 

 

PG
Reeza
Super User
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
FreelanceReinh
Jade | Level 19

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 468 views
  • 1 like
  • 4 in conversation