merging datasets

[Bintang18]

Hi SAS experts,

I have 4 datasets: q1, q2, q3 and q4 contains homeless/ primary residence, entrydate/exit dates among others.

q1    id                                        q2                                           q3                                         q4

id      homelessind          id       homelessind                         id  homelessind                      id homelessind

1            y                       2              y                                    3          y                                       1        y

2            y                        4             y                                    11         y                                       2        y

3           y                         6             y                                     5          y                                        4       y

3           y                         7             y                                      8        y                                        5        y

5                                                                                                                                                 7       y

9            y

I need to capture all of the students who were homeless and/or have no primary residence at some point during school year:

id            q1            q2            q3            q4

1              y                                              y

2              y              y                              y

3              y                               y

4                              y                              y

5             y                                y              y

........

can someone help me with this? I tried proc sql full join by id and  I got tons of duplicates. I tried left join and missing some of the data. Also how do I run dupkey when  I have hundreds of missing information....  Thank you in advance!

[Kurt_Bremser]

Don't put the data side-by-side, stack it vertically:

data q1;
input id homelessind $;
cards;
1 y
;
run;

data q2;
input id homelessind $;
cards;
2 y
;
run;

data q3;
input id homelessind $;
cards;
3 y
;
run;

data q4;
input id homelessind $;
cards;
1 y
2 y
;
run;

data want;
set
  q1
  q2
  q3
  q4
  indsname=quarter
;
qu = scan(quarter,2,'.');
run;

proc sort data=want;
by id qu;
run;

[Bintang18]

Thanks, I really appreciate your help. I am off site, no access to SAS so I can't really apply the formula. But if you don't mind, each dataset has about 20,000 records and 21 variables - some with redundant ids because students moved from district to district/school, but their state_id remains. The formula  still apply, right??? Thank you again!

[Bintang18 · Accepted solution]

Hello,

This syntax resolved my problem, have to do some transpose but the foundation is there.

 Thank you!