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:
ID | Name | Gender | Lab_ID | Lab | Result | Phone_ID | Phone |
1 | Sam | Male | 1 | DNA | Positive | 1 | 1111111111 |
1 | Sam | Male | 1 | DNA | Positive | 2 | 2222222222 |
1 | Sam | Male | 2 | RNA | Positive | 1 | 1111111111 |
1 | Sam | Male | 2 | RNA | Positive | 2 | 2222222222 |
1 | Sam | Male | 3 | VIRAL | Negative | 1 | 1111111111 |
1 | Sam | Male | 3 | VIRAL | Negative | 2 | 2222222222 |
Results I want:
ID | Name | Gender | Lab1 | Lab2 | Lab3 | Result1 | Result2 | Result3 | Phone1 | Phone2 |
1 | Sam | Male | DNA | RNA | VIRAL | Positive | Positive | Negative | 1111111111 | 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
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;
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;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.