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
... View more
