How Do I Join and Transpose Multiple Tables Each With Repeated Measures?

[cgates]

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

[andreas_lds · Accepted solution]

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]

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.