topic merging datasets in SAS Data Management

merging datasets

Bintang18 — Mon, 12 Nov 2018 04:54:45 GMT

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!

Re: merging datasets

Kurt_Bremser — Mon, 12 Nov 2018 08:07:03 GMT

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;

Re: merging datasets

Bintang18 — Mon, 12 Nov 2018 17:17:08 GMT

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!

Re: merging datasets

Bintang18 — Tue, 13 Nov 2018 16:54:37 GMT

Hello,

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

Thank you!