BookmarkSubscribeRSS Feed
helloagainoh2
Calcite | Level 5

Hi I am wanting to access elements from another data set while working with a data set unrelated to the one I want to access. 

 

Here's an example, I want to modify the column report_on if there is an id that is in common to both data sets. If there is an id in common then I want report_on to be labeled "Yes"

 

I am not thinking of a merge because one of the data sets is a key that has 5 id's, and the data set I am searching through is thousands of observations long.

 

I am having to do some conditional statement work, the id's have decimal points so I am wanting to do a substr and look at the last 4 digits, if they are all 0000's then report yes, but if the last 4 digits of the id is not all 0's then search for an exact match.

 

I think my problem is accessing the other data set to compare values, I thought of using a proc sql and a subquery, but I am not sure how to update the report_on column doing that.


data one;
input id age nickname $ Report_on$;
datalines;
13.0000 25 j
2.2562 35 e
3 53 n
7 21 p
11 31 z
;
run;

 

data two;
input id salary name$;
datalines;
1 2000 jimmy
2.2562 3000 jeff
4 543 leory
13.0000 33 joey
;
run;

1 REPLY 1
Shmuel
Garnet | Level 18

If you read the ID as numeric you don't need to care for the decimal digits, it is implicit, and you can use MERGE as long as the IDs are unique, at least in one of the tables. Check output of next code:

data one;
infile datalines truncover;
input id age nickname $ Report_on $;
datalines;
13.0000 25 j
2.2562 35 e
3 53 n
7 21 p
11 31 z
;
run;

 

data two;
input id salary name $;
datalines;
1 2000 jimmy
2.2562 3000 jeff
4 543 leory
13.0000 33 joey
;
run;

proc sort data=one; by id; run;
proc sort data=two; by id; run;
data want;
 merge one(in=in1) two(in=in2);
   by id;
      if in1 and in2 then repoer_on = 'YES';
     /* OR: if in1 and in2 then do; report_on = 'YES'; output; end;
run;
   

 

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 338 views
  • 0 likes
  • 2 in conversation