BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
NewUsrStat
Lapis Lazuli | Level 10

Hi guys, 

suppose to have the following dataset: 

 

data DB1;
input ID :$20. ;
cards;
8
8
8 
170
170
170
166
166
166
;
run;

and a list: 

data DB2;
input ID :$20. ;
cards;
8
170
152
166
;
run;

Is there a way to match DB1 with DB2 and every time IDs of DB2 are found in DB1 a suffix "999" will be added?

Desired output: 

 

data DB3;
input ID :$20. ;
cards;
8999
8999
8999 
170999
170999
170999
166999
166999
166999
;
run;

Thank you in advance. 

 

Best

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15
data db3_test;
  if 1=_N_ then
    do;
      declare hash h(dataset: "db2");
      h.defineKey('ID');
      h.defineDone();
    end;
  set db1;

  if h.check()=0 then id = cats(id,'999');
run;
proc print;
run;
_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

3 REPLIES 3
yabwon
Onyx | Level 15
data db3_test;
  if 1=_N_ then
    do;
      declare hash h(dataset: "db2");
      h.defineKey('ID');
      h.defineDone();
    end;
  set db1;

  if h.check()=0 then id = cats(id,'999');
run;
proc print;
run;
_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



yabwon
Onyx | Level 15

According to your description this:

170999
170999
170999

should not exist in DB3.

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



NewUsrStat
Lapis Lazuli | Level 10
Sorry I edited. I made a mistake!

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

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
  • 3 replies
  • 630 views
  • 1 like
  • 2 in conversation