/*Pollutants*/
data d1;
input id$ 1-5
job 7 id_job$ 9-15 hcl_exp 17 amo_exp 19 bio_exp 21 cla_exp 23;
datalines;
OSa03 4 OSa03_4 1 0 0 0
OSa06 3 OSa06_3 0 1 0 0
OSa13 1 OSa13_1 0 1 1 0
OSa13 3 OSa13_3 0 1 1 1
OSa29 2 OSa29_2 0 0 0 1
OSa29 4 OSa29_4 0 1 1 0
OSa30 4 OSa30_4 0 0 1 0
OSa30 1 OSa30_1 1 0 0 0
OSa30 2 OSa30_2 0 1 1 1
OSa54 3 OSa54_3 0 1 0 0
OSa64 3 OSa64_3 0 1 0 0
OSa73 3 OSa73_3 0 0 0 1
OSa74 3 OSa74_3 1 0 0 0
OSa78 3 OSa78_3 0 1 0 0
;
proc sort data=d1; by id; run;
/* Cancer subjects*/
data d2;
input id$ 1-5 lung$ 7-15;
datalines;
OSa01 Pop cont
OSa06 Ca cont
OSa11 Pop cont
OSa13 Ca case
OSa29 Ca cont
OSa30 Ca case
OSa31 Ca cont
OSa54 Pop cont
OSa73 Pop cont
;
proc sort data=d2; by id; run;
/* Exposure level*/
data d3;
input id$ 1-5 job 7 idchem 9 level 16;
datalines;
OSa03 4 211701 3
OSa06 3 210701 3
OSa13 1 210701 3
OSa13 1 990021 3
OSa13 3 210701 3
OSa13 3 990005 3
OSa13 3 990021 2
OSa29 2 990005 3
OSa29 4 210701 3
OSa30 1 990021 3
OSa30 2 211701 3
OSa30 3 210701 3
OSa30 3 990005 3
OSa30 3 990021 3
OSa54 3 990005 3
OSa64 3 210701 2
OSa74 1 211701 3
OSa78 4 210701 3
OSa78 4 990005 3
OSa78 4 990021 3
;
proc sort data=d3; by id; run;
/* Exposure Duration*/
data d4;
input id$ 1-5 idchem 7 status$ 14-15 duration 16-18;
datalines;
OSa03 211701 S 6
OSa06 210701 S 9
OSa13 210701 S 37
OSa13 990005 S 5
OSa13 990021 S 37
OSa29 210701 NS 12
OSa29 990005 S 2
OSa30 210701 S 8
OSa30 211701 NS 8
OSa30 990005 S 8
OSa30 990021 S 15
OSa54 210701 NS 14
OSa64 210701 S 15
OSa74 211701 NS 21
OSa78 210701 NS 20
OSa78 990005 S 20
OSa78 990021 S 20
OSa86 990005 S 14
OSa93 210701 S 4
OSa93 990005 S 13
;
The IDs are not unique in your data sets. This is the reason you get the message
MERGE statement has more than one data set with repeats of BY values
In such case there is different approach of SAS base and SQL.
Are there another variables beyond ID to make it unique?
otherwise use SQL full join to merge the data sets.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.