Talking about 'efficiently', there still is a Hash() approach, which does enforce the new rule:
data port;
input port_id @@;
cards;
10 10 10 33 33
;
data bench;
input port_id bench_id;
cards;
10 33
33 44
;
data want;
if _n_=1 then do;
if 0 then set bench;
dcl hash b(dataset: 'bench(keep=bench_id)');
b.definekey('bench_id');
b.definedone();
dcl hash p(dataset: 'bench(keep=port_id)');
p.definekey('port_id');
p.definedone();
end;
set port;
if b.check(key:port_id)=0 then new_id=cats('b_',port_id);
else if p.check(key:port_id)=0 then new_id=cats('p_',port_id);
else new_id=cats(port_id);
keep port_id new_id;
run;
proc print;run;
Haikuo
Haikuo:
Data set PORT does not have a variable named BENCH, so bench is always missing, and b.check() will never be 0. No new_id's will be "b". Do you mean b.check(key:port_id)?
Good Catch, Mark. UPDATING...
Haikuo
I understand the task to be to creating a new id in which the prefix is always a "P" except when port_id matches any existing bench_id, right?
If so, and port_id can be any integer between 1 and (say) 1000, then the simplest approach is to use a "key lookup" technique (i.e. an array in the example below):
%let max_port=1000;
data want;
array letr {&max_port} $1 _temporary_ (&max_port * 'P');
if _n_=1 then do until (end_of_bench);
set bench end=end_of_bench;
letr{bench_id}='B';
end;
set port;
new_id=cats(letr{port_id},port_id);
run;
If instead it is to prefix 'P' only when port_id is in the list of ports in BENCH, 'B' when it's in the bench list, and otherwise no prefix, (I saw examples of this) then it's a minor change:
data want;
array letr {&max_port} $1 _temporary_;
if _n_=1 then do until (end_of_bench);
set bench end=end_of_bench;
letr{port_id}='P';
letr{bench_id}='B';
end;
set port;
new_id=cats(letr{port_id},port_id);
run;
Nice trick, Mark! I like it, I remember seeing it from your post @SAS-L. And I assume you forgot putting in 'end=end_of_bench' in your first data step
Haikuo
Haikuo:
Thanks for the head's up. Oversight corrected.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.