topic Re: Need Data Step Help in SAS Programming

Need Data Step Help

GPatel — Sat, 04 Mar 2017 23:42:27 GMT

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.

Re: Need Data Step Help

Reeza — Sat, 04 Mar 2017 23:49:15 GMT

How exactly is the matching occuring?

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

Re: Need Data Step Help

GPatel — Sun, 05 Mar 2017 00:35:50 GMT

Dear Rezza:

Thanks for your response. It should be second Row.

Thanks.

Re: Need Data Step Help

Reeza — Sun, 05 Mar 2017 01:22:55 GMT

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.

Re: Need Data Step Help

art297 — Sun, 05 Mar 2017 02:59:37 GMT

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

Re: Need Data Step Help

Ksharp — Sun, 05 Mar 2017 04:26:32 GMT

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;

Re: Need Data Step Help

GPatel — Sun, 05 Mar 2017 16:16:51 GMT

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

Re: Need Data Step Help

GPatel — Sun, 05 Mar 2017 16:21:29 GMT

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

Re: Need Data Step Help

art297 — Mon, 06 Mar 2017 00:07:55 GMT

@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

Re: Need Data Step Help

Ksharp — Mon, 06 Mar 2017 02:13:19 GMT

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 .

Re: Need Data Step Help

art297 — Mon, 06 Mar 2017 02:53:35 GMT

Xia,

Fair enough!

Art