I have this data set to deal with, its kinda complicated to explain but I will try my best. Please let me know if I need to elobrate more.
Below I have pasted in what the data sets I have (this is just a small set I made up the actual data set has thousands of rows) and what the desired output needs to be. Basically the variables in Pid (data set b) match some of the variables in Mid (data set a). I need to create a column (F1) where when Mid=Pid the corresponding row is equal to 1, and if the Pid is missing the corresponding row in F1 is 0. I have tried doing this in proq sql, but proc sql is very new to me. I really appreciate any sort of guidance or help.
Thanks in advance!
Data set a
Mid | Cid |
5 | 36 |
8 | 46 |
16 | 65 |
17 | 69 |
29 | 78 |
31 | 85 |
48 | 90 |
53 | 99 |
56 | 115 |
58 | 123 |
63 | 142 |
71 | 151 |
180 | 160 |
540 | 169 |
120 | 173 |
160 | 181 |
175 | 190 |
142 | 200 |
Data set b
Pid |
8 |
16 |
29 |
48 |
53 |
56 |
63 |
71 |
180 |
540 |
120 |
What I want to get
Mid | Cid | Fid |
5 | 36 | 0 |
8 | 46 | 1 |
16 | 65 | 1 |
17 | 69 | 0 |
29 | 78 | 1 |
31 | 85 | 0 |
48 | 90 | 1 |
53 | 99 | 1 |
56 | 115 | 1 |
58 | 123 | 0 |
63 | 142 | 1 |
71 | 151 | 1 |
180 | 160 | 1 |
540 | 169 | 1 |
120 | 173 | 1 |
160 | 181 | 0 |
175 | 190 | 0 |
142 | 200 | 0 |
You need a left join:
data a;
input Mid Cid;
datalines;
5 36
8 46
16 65
17 69
29 78
31 85
48 90
53 99
56 115
58 123
63 142
71 151
180 160
540 169
120 173
160 181
175 190
142 200
;
data b;
input Pid;
datalines;
8
16
29
48
53
56
63
71
180
540
120
;
proc sql;
create table c as
select
a.*,
b.Pid is not missing as Fid
from
a left join
b on a.Mid=b.Pid;
select * from c;
quit;
A SQL Left Join is what you need, plus a CASE statement to identify the source record.
data have1;
input Mid Cid;
cards;
5 36
8 46
16 65
17 69
29 78
31 85
48 90
53 99
56 115
58 123
63 142
71 151
180 160
540 169
120 173
160 181
175 190
142 200
;
run;
Data have2;
input Pid;
cards;
8
16
29
48
53
56
63
71
180
540
120
;
run;
proc sql;
create table want as
select h1.*,
case when missing(h2.pid) then 0
else 1
end as flag
from have1 as h1
left join have2 as h2
on h1.mid=h2.pid;
quit;
Thanks so much for the help! I didn't know about the case statement for proc sql.
You need a left join:
data a;
input Mid Cid;
datalines;
5 36
8 46
16 65
17 69
29 78
31 85
48 90
53 99
56 115
58 123
63 142
71 151
180 160
540 169
120 173
160 181
175 190
142 200
;
data b;
input Pid;
datalines;
8
16
29
48
53
56
63
71
180
540
120
;
proc sql;
create table c as
select
a.*,
b.Pid is not missing as Fid
from
a left join
b on a.Mid=b.Pid;
select * from c;
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.