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

I'm using SAS EG Version 7.15 HF8 (7.100.5.6214) (64-bit). I often have to join multiple tables that each have repeated measures and then I have to transform the data from long to wide. I can get the output I need, but the only way I know how to do this is to join the 1st repeated measures table, transform to wide format, then join the next repeated measures table, transform to wide format, and so forth so that the new numbered variables are correct.

 

Is there a way I can join all of my tables together at once and then transform everything to wide at one time? Example data below.

 

Table 1

ID Name Gender

1   Sam   Male

 

Table 2

ID   Lab_ID    Lab     Result

1      1            DNA    Positive

1      2            RNA    Positive

1      3            Viral     Negative

 

Table 3

ID    Phone_ID   Phone

1      1                 1111111111

1      2                 2222222222

 

Result of joining three tables:

 IDNameGender

Lab_ID

LabResultPhone_IDPhone 
1SamMale1DNAPositive11111111111
1SamMale1DNAPositive22222222222
1SamMale2RNAPositive11111111111
1SamMale2RNAPositive22222222222
1SamMale3VIRALNegative11111111111
1SamMale3VIRALNegative22222222222

 

Results I want:

IDNameGenderLab1Lab2Lab3Result1Result2Result3Phone1Phone2
1SamMaleDNARNAVIRALPositivePositiveNegative1111111111  2222222222

 

SAS Code I currently use for transforming to the results I want

proc sql;
create table first as
select table1.*, lab, result
from table1 join table2 on table1.ID = table2.ID
;quit

proc sql noprint;
select max(obs) into :nobs from (select count(*) as obs from first group by ID);
quit;

proc summary nway data=first missing;
class ID--Gender;
output out=wide(drop=_type_ _freq_) idgroup(out[&nobs] (Lab Result)=);
run;

proc sql;
create table second as
select first.*,phone
from first join table3 on first.ID = table3.ID
;quit

proc sql noprint;
select max(obs) into :nobsfin from (select count(*) as obs from second group by ID);
quit;

proc summary nway data=second missing;
class ID--Lab&nobsfin;
output out=wide_again(drop=_type_ _freq_) idgroup(out[&nobs] (Phone)=);
run;

What I'd love to do is join all of my data together like below and then transform to the results I want.

proc sql;
create table all as
select table1.*, lab, result, phone
from table1 join table2 on table1.ID = table2.ID
join table3 on table1.ID = table3.ID
;quit

 

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

Do you want the result as dataset or report? For a dataset the structure will cause additional work while further processing it. But here you go:

 

data persons;
   length ID 8 Name Gender $ 10;
   input ID Name Gender;
   datalines;
1 Sam Male
;
run;

data results;
   length ID Lab_ID 8 Lab Result $ 10;
   input ID Lab_ID Lab Result;
   datalines;
1 1 DNA Positive
1 2 RNA Positive
1 3 Viral Negative
;
run;


data contact;
   length ID Phone_ID 8 Phone $ 20;
   input ID Phone_ID Phone;
datalines;
1 1 1111111111
1 2 2222222222
;
run;

proc transpose data=work.results out=results_Lab(drop=_name_) prefix=Lab;
   by ID;
   var Lab;
run;

proc  transpose data=work.results out=results_Result(drop=_name_) prefix=Result;
   by Id;
   var Result;
run;

proc transpose data=contact out=contact_Phone(drop=_name_) prefix=Phone;
   by id;
   var Phone;
run;

data combined;
   merge persons results_Lab results_Result contact_Phone;
   by Id;
run;

View solution in original post

2 REPLIES 2
andreas_lds
Jade | Level 19

Do you want the result as dataset or report? For a dataset the structure will cause additional work while further processing it. But here you go:

 

data persons;
   length ID 8 Name Gender $ 10;
   input ID Name Gender;
   datalines;
1 Sam Male
;
run;

data results;
   length ID Lab_ID 8 Lab Result $ 10;
   input ID Lab_ID Lab Result;
   datalines;
1 1 DNA Positive
1 2 RNA Positive
1 3 Viral Negative
;
run;


data contact;
   length ID Phone_ID 8 Phone $ 20;
   input ID Phone_ID Phone;
datalines;
1 1 1111111111
1 2 2222222222
;
run;

proc transpose data=work.results out=results_Lab(drop=_name_) prefix=Lab;
   by ID;
   var Lab;
run;

proc  transpose data=work.results out=results_Result(drop=_name_) prefix=Result;
   by Id;
   var Result;
run;

proc transpose data=contact out=contact_Phone(drop=_name_) prefix=Phone;
   by id;
   var Phone;
run;

data combined;
   merge persons results_Lab results_Result contact_Phone;
   by Id;
run;
cgates
Obsidian | Level 7
Thank you!

It works. I guess there’s no one step method for transposing all at once. It’s good to know that the solutions require separate transposing steps.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 2 replies
  • 699 views
  • 2 likes
  • 2 in conversation