BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GPatel
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

10 REPLIES 10
Reeza
Super User

How exactly is the matching occuring?


CBS 300 has more than one match that I see, could be either the 2nd or 3rd row?

GPatel
Pyrite | Level 9

Dear Rezza:

 

Thanks for your response. It should be second Row. 

 

Thanks.

Reeza
Super User

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. 

art297
Opal | Level 21

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

 

Ksharp
Super User

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;
GPatel
Pyrite | Level 9

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

GPatel
Pyrite | Level 9

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

 

art297
Opal | Level 21

@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

 

Ksharp
Super User

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 .

art297
Opal | Level 21

Xia,

 

Fair enough!

 

Art

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1251 views
  • 0 likes
  • 4 in conversation