topic Loop in a merge (Or kind of Vlookup) in SAS Programming

Loop in a merge (Or kind of Vlookup)

GuiVtzl — Mon, 21 Jul 2014 14:17:18 GMT

Hi, I have to do one calculation and I am completely stuck... Here is what I need :

I have two tables :

- table A : with row ID ID3

- table B : with row ID2

What I need to do is 20 iterations (that number is mandatory) that consist in :

iteration 1 : check for each line in B if ID2 is in ID =>

if it is then (a new variable in B, let's call it NEW) NEW=ID3

if it is not then NEW=ID2

iteration 2 : check if NEW is in ID =>

if it is then NEW=ID3 (the "new" ID3, corresponding to the "new" ID)

if not then NEW=NEW

and so on until 20...

I really don't know how to do that. Could you help me please ? thanks a lot

Here's an exemple

Table A

ID ID3

25A 35M

26B 30C

30C 23P

Table B 1st iteration

ID2 NEW

25A 35M

26B 30C

Table B 2nd iteration

ID2 NEW

25A 35M

26B 23P

and so on..

Re: Loop in a merge (Or kind of Vlookup)

Tom — Mon, 21 Jul 2014 14:28:52 GMT

Looks like table A represents changes to the ID values? So 26B became 30C which became 23P.

Do you want to get to the final value? Or do you want to include all of the intermediate values?

Re: Loop in a merge (Or kind of Vlookup)

RW9 — Mon, 21 Jul 2014 14:31:31 GMT

Hi,

I am struggling to follow your logic after the first iteration. For iteration 1:

proc sql;

create table FIRST as

select A.*

from TABLEA A

right join TABLEB B

on A.ID=B.ID2;

quit;

Then after that you are probably better off creating a small macro:

%macro Do_your_logic (n=);

...

%mend Do_your_logic;

And calling it 20 times:

data _null_;

do I=1 to 20;

call execute('%Do_your_logic(n='||strip(put(I,best.))||');');

end;

run;

Re: Loop in a merge (Or kind of Vlookup)

Astounding — Mon, 21 Jul 2014 14:40:51 GMT

Simplest would be to convert Table A into a format (although a hash table would work reasonably well):

data table_A;

set table_A;

fmtname='$id';

rename id=start id3=label;

run;

proc format cntlin=table_A;

run;

Next, use a DATA step to apply the format. Perform one iteration to see if NEW actually changes, then perform the remaining 19 iterations if necessary:

data want;

set table_B;

new = put(id2, $id.);

if new ne id2 then do _n_=1 to 19;

new = put(new, $id.);

end;

run;

You may need to learn about some of these tools, but at least the final program is relatively short and straightforward. Good luck.

Re: Loop in a merge (Or kind of Vlookup)

RW9 — Mon, 21 Jul 2014 14:57:24 GMT

Or something like:

data tablea;
attrib id id3 format=$5.;
id="25A"; id3="35M"; output;
id="26B"; id3="30C"; output;
id="30C"; id3="23P"; output;
run;
data tableb;
attrib id2 format=$5.;
array new{20} $5.;
id2="25A"; output;
id2="26B"; output;
run;

data _null_;
do i=1 to 20;
    if i=1 then do;
      call execute('proc sql;
                      update TABLEB
                      set NEW1=(select ID3 from TABLEA where TABLEB.ID2=TABLEA.ID);
                    quit;');
    end;
    else do;
      call execute('proc sql;
                      update TABLEB
                      set NEW'||strip(put(i,best.))||'=(select ID3 from TABLEA where TABLEB.NEW'||strip(put(i-1,best.))||'=TABLEA.ID);
                    quit;');
    end;
end;
run;

Re: Loop in a merge (Or kind of Vlookup)

GuiVtzl — Mon, 21 Jul 2014 14:58:07 GMT

Thanks Tom and RW9,

I have asked my boss, and it can be both !

in fact, table B can be both following ways :

keep intermediate values

With a structure like

ID2 NEW_1 NEW_2 - ... - NEW_20 NEW_HIGH

where each NEW_i reflects the successive changes in ID2 and, in the end, NEW_HIGH would be equal to NEW_20 (We need it to be called NEW_HIGH for our work)

without intermediate values

With a structure like

ID2 NEW_HIGH

where NEW_HIGH would be equivalent to the NEW_20 above

I really don't see how to do that. I thought about merges but I can't figure out how to keep ID which doesn't change at all in that case.

Do you see how to do that ? Both ways are ok (if you can, the simpliest one would be the best ahah)

Thanks a lot

Re: Loop in a merge (Or kind of Vlookup)

Patrick — Tue, 22 Jul 2014 07:06:42 GMT

I have had a related problem in the past. Here what the Forum came up with

Re: Loop in a merge (Or kind of Vlookup)

GuiVtzl — Tue, 22 Jul 2014 07:15:49 GMT

I would like to thank everyone here !
All of your answers have been very inspirational !

I applied the one provided by Astounding, and it worked just perfectly !

I have to say I have been quite amazed by that trick ! I called some of my colleagues to check on it and they were all "wooow".

Do you have any links or resources where I could learn more tools like that ?

Thanks a lot again, have a good day !

Re: Loop in a merge (Or kind of Vlookup)

Tom — Tue, 22 Jul 2014 13:22:16 GMT

One way is to convert your mapping table into a format.

Then in a data step loop testing the formatted value until you do not find any further recodes.

proc format ;

value $id

'25A'='35M'

'26B'='30C'

'30C'='23P'

other=' '

;

run;

data test;

input id $ ;

new=id;

put id @ ;

do while (put(new,$id.) ne ' ') ;

new=put(new,$id.);

put '-> ' new @;

end;

put;

cards;

25A

26B

run;

25A -> 35M

26B -> 30C -> 23P

Re: Loop in a merge (Or kind of Vlookup)

Ksharp — Tue, 22 Jul 2014 13:24:04 GMT

If it was one point to one point , that would be easy. Let me know if it worked.

data a;
input ID      $              ID3 $;
cards;
25A                   35M
26B                    30C
30C                    23P
;
run;
data b;
input ID2 $ ;
cards;
25A 
26B 
;
run;
data want(keep=id2 new); 
 if _n_ eq 1 then do;
  if 0 then set a;
  declare hash ha(dataset:'a');
   ha.definekey('id');
   ha.definedata('id3');
   ha.definedone();
 end;
set b;
id=id2;     id3=' ';
do until(rc ne 0);
 rc=ha.find();id=id3;
end;
new=id3;
run;

Xia Keshan

Re: Loop in a merge (Or kind of Vlookup)

Astounding — Tue, 22 Jul 2014 14:00:06 GMT

GuiVtzl,

I think you already found the right place for ideas and techniques ... reading posts on this forum!