DATA Step, Macro, Functions and more

Need Data Step Help

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Need Data Step Help

[ Edited ]

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.


Accepted Solutions
Solution
‎03-05-2017 12:53 PM
Super User
Posts: 10,020

Re: Need Data Step Help

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


All Replies
Super User
Posts: 19,772

Re: Need Data Step Help

How exactly is the matching occuring?


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

Contributor
Posts: 69

Re: Need Data Step Help

Dear Rezza:

 

Thanks for your response. It should be second Row. 

 

Thanks.

Super User
Posts: 19,772

Re: Need Data Step Help

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. 

PROC Star
Posts: 7,468

Re: Need Data Step Help

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

 

Solution
‎03-05-2017 12:53 PM
Super User
Posts: 10,020

Re: Need Data Step Help

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;
Contributor
Posts: 69

Re: Need Data Step Help

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

Contributor
Posts: 69

Re: Need Data Step Help

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

 

PROC Star
Posts: 7,468

Re: Need Data Step Help

@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

 

Super User
Posts: 10,020

Re: Need Data Step Help

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 .

PROC Star
Posts: 7,468

Re: Need Data Step Help

Xia,

 

Fair enough!

 

Art

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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