topic Re: create new column in a data set where same variables between two data sets are identified with a in SAS Programming

create new column in a data set where same variables between two data sets are identified with a 1

IsoscelesKramer — Tue, 25 Apr 2017 02:34:06 GMT

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

Re: create new column in a data set where same variables between two data sets are identified with a

Reeza — Tue, 25 Apr 2017 02:54:30 GMT

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;

Re: create new column in a data set where same variables between two data sets are identified with a

PGStats — Tue, 25 Apr 2017 03:04:11 GMT

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;

Re: create new column in a data set where same variables between two data sets are identified with a

IsoscelesKramer — Tue, 25 Apr 2017 20:55:02 GMT

Thanks so much for the help! I didn't know about the case statement for proc sql.

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

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

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

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

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

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