BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Bintang18
Obsidian | Level 7

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Bintang18
Obsidian | Level 7

Hello,

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

 Thank you! 

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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
Obsidian | Level 7

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
Obsidian | Level 7

Hello,

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

 Thank you! 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1162 views
  • 0 likes
  • 2 in conversation