Updating column in table if matching values occur in another table

[aamirkc]

Hello,

I have the following table CUST:

Panid    Age  Buy

1           45

2           50

3           70

4           80

5           33

6           20

The Buy column is empty. I would like to update it to values 0 or 1 based on if Panid exists in another table BUYFR:

Panid      Category

3          Frozen food

5          Frozen food

6          Frozen food

The code should update variable Buy corresponding to variable Panid 3,5 and 6 to 1. Rest all Should be zero. The Final output should look similar to :

Panid    Age  Buy

1           45    0

2           50    0

3           70    1

4           80    0   

5           33    1

6           20    1

Please let me know how this can be implemented in SAS?

[Ksharp]

data CUST;
infile cards truncover;
input Panid    Age  Buy     ;
cards;
1           45
2           50
3           70
4           80
5           33
6           20
;
data BUYFR;
input Panid      Category & $40.;
cards;
3          Frozen food
5          Frozen food
6          Frozen food
;
run;
data want;
 if _n_ eq 1 then do;
 declare hash h(dataset:'BUYFR');
 h.definekey('Panid');
 h.definedone();
 end;
set CUST;
Buy=ifn(h.check()=0,1,0);
run;

Xia Keshan

Message was edited by: xia keshan

[Haikuo]

"Not" should be faster than IFN()?

Buy=not h.check();

Haikuo

[Loko]

Hello,

One way:

data have;
input panid Age;
buy=0;
datalines;
1 45
2 50
3 70
4 80
5 33
6 20
;

data transact;
infile datalines truncover;
input panid Category $20.;
datalines;     
3 Frozen food
5 Frozen food
6 Frozen food
;

data want;
merge have (in=h) transact (in=t drop=category);
by panid;
if h and t then buy=1;
run;

[Haikuo]

I like it. If both incoming are presorted, this can be the winner on performance. One way to avoid preassigning "buy=0" in "have" is:

data want;

merge have (in=h) transact (in=t drop=category);

by panid;

buy=(h and t);

run;

[chrej5am]

Or in sql update ...

data CUST;

format buy best32.;

Panid=1;Age=45;output;

Panid=2;Age=50;output;

Panid=3;Age=70;output;

Panid=4;Age=80;output;

Panid=5;Age=33;output;

Panid=6;Age=20;output;

run;

data BUYFR;

Panid=3;Category='Frozen food';output;

Panid=5;Category='Frozen food';output;

Panid=6;Category='Frozen food';output;

run;

proc sql;

update CUST as u

   set buy=(select count(*) from BUYFR as n

  where u.Panid=n.Panid);

quit;

Jakub

[PoornimaRavishankar]

data cust;

infile cards missover;

input panid  age ;

datalines;

1           45

2           50

3           70

4           80

5           33

6           20

;

data buyfr;

infile cards missover;

input panid  category  $20.; <--- Modify length of category as required

datalines;

3          Frozen food

5          Frozen food

6          Frozen food

;

run;

proc sql;

create table cust as

select a.panid, age, buy as (a.panid = b.panid)            <--------- Will this work?

from cust a left join buyfr b on a.panid = b.panid;

quit;

Sorry, I don't have a way to test the program at the moment. I am expecting this to work, given SAS's flexibility with programming. If not, well, there are other solutions already listed.