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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.