Dear SAS Community Members:
I have two SAS data sets VNDR and LKUP as shown below.
data VNDR;
Input Name : $ ID City & $11. St : $2. Amt;
datalines;
CNN 100 Atlanta GA 200
ABC 200 Washington DC 150
CBS 300 New York NY 250
CNBC 400 Houston TX 400
;
RUn;
options missing=.;
Data LKup;
infile cards missover;
input CNN ABC CBS CNBC;
cards;
100 . 150 .
. 200 300 350
500 200 300 .
10 20 . 400
;
run;
I would like to have output as shown below:
Match Name / ID from VNDR dataset to LKUP dataset. If Name and ID matches,
concatenate values of name in a string say Remark seperated by delimeter ';'
Desired Output :
Name | ID | City | State | Amt | Remark |
CNN | 100 | Atlanta | GA | 200 | CNN:100;ABC:.;CBS:150;CNBC:. |
ABC | 200 | Washington | DC | 150 | ABC:200;CNN:.;CBS:300;CNBC:350 |
CBS | 300 | New York | NY | 250 | CBS:300;CNN:500;ABC:200;CNBC:. |
CNBC | 400 | Houston | TX | 400 | CNBC:400;CNN:10;ABC:20;CBS:. |
Please note that the order of Name in Remark field is not important.
Thanks in advance.
Change something based on Arthur's code.
data VNDR;
informat Name $4.;
informat City $11.;
informat St $2.;
Input Name ID City & St Amt;
datalines;
CNN 100 Atlanta GA 200
ABC 200 Washington DC 150
CBS 300 New York NY 250
CNBC 400 Houston TX 400
;
Data LKup;
infile cards missover;
input CNN ABC CBS CNBC;
cards;
100 . 150 .
. 200 300 350
500 200 300 .
10 20 . 400
;
data want;
set VNDR;
set LKup;
run;
data want (keep=name city st amt want);
set want;
array stuff{*} CNN--CNBC;
length rem want temp $255.;
do i=1 to dim(stuff);
if name=vname(stuff{i}) and id=stuff{i} then n=i;
item=catt(vname(stuff{i}),': ',stuff{i});
rem=catx('; ',rem,item);
end;
call scan(rem,n,p,l,';');
temp=substr(rem,p,l);
want=cats(temp,';',substrn(rem,1,p-1),substrn(rem,p+l+1));
run;
How exactly is the matching occuring?
CBS 300 has more than one match that I see, could be either the 2nd or 3rd row?
Dear Rezza:
Thanks for your response. It should be second Row.
Thanks.
So the rule is first occurrence? Order matters then?
Note that your sample data is numeric but your text seems to indicate that they may be character values. Any solution would need to be modified if this was the case.
I think that the following does what you want:
data VNDR; informat Name $4.; informat City $11.; informat St $2.; Input Name ID City & St Amt; datalines; CNN 100 Atlanta GA 200 ABC 200 Washington DC 150 CBS 300 New York NY 250 CNBC 400 Houston TX 400 ; Data LKup; infile cards missover; input CNN ABC CBS CNBC; cards; 100 . 150 . . 200 300 350 500 200 300 . 10 20 . 400 ; data want; set VNDR; set LKup; run; data want (keep=name city st amt rem); set want; array stuff CNN--CNBC; length rem $255.; do over stuff; item=catt(vname(stuff),': ',stuff); rem=catx('; ',rem,item); end; run;
Art, CEO, AnalystFinder.com
Change something based on Arthur's code.
data VNDR;
informat Name $4.;
informat City $11.;
informat St $2.;
Input Name ID City & St Amt;
datalines;
CNN 100 Atlanta GA 200
ABC 200 Washington DC 150
CBS 300 New York NY 250
CNBC 400 Houston TX 400
;
Data LKup;
infile cards missover;
input CNN ABC CBS CNBC;
cards;
100 . 150 .
. 200 300 350
500 200 300 .
10 20 . 400
;
data want;
set VNDR;
set LKup;
run;
data want (keep=name city st amt want);
set want;
array stuff{*} CNN--CNBC;
length rem want temp $255.;
do i=1 to dim(stuff);
if name=vname(stuff{i}) and id=stuff{i} then n=i;
item=catt(vname(stuff{i}),': ',stuff{i});
rem=catx('; ',rem,item);
end;
call scan(rem,n,p,l,';');
temp=substr(rem,p,l);
want=cats(temp,';',substrn(rem,1,p-1),substrn(rem,p+l+1));
run;
Thanks a million to both Rezza and KSharp. Their timely elegance solutions depicts their smartness, skills and service to SAS communtiy.
I am very proud !
Girish Patel
Sorry ....I appolize, I forgot to mention. My sincere thanks too to Mr. Art (CEO, AnalystFinder.com). He did provide timely and great solution to my question.
Thanks,
Girish Patel
@Ksharp Xia, since the OP originally stated "Please note that the order of Name in Remark field is not important.", why did you post the revised code? Just curious.
Art
Arthur,
Maybe I just ignore these words, or don't understand what OP mean. I just notice the order of names in remark and replicate it .
Xia,
Fair enough!
Art
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.