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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.