topic Re: Updating column in table if matching values occur in another table in SAS Programming

Updating column in table if matching values occur in another table

aamirkc — Wed, 28 Jan 2015 09:37:53 GMT

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?

Re: Updating column in table if matching values occur in another table

Ksharp — Wed, 28 Jan 2015 09:55:52 GMT

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

Re: Updating column in table if matching values occur in another table

Loko — Wed, 28 Jan 2015 10:02:53 GMT

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;

Re: Updating column in table if matching values occur in another table

chrej5am — Wed, 28 Jan 2015 10:05:29 GMT

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

Re: Updating column in table if matching values occur in another table

PoornimaRavishankar — Wed, 28 Jan 2015 16:33:25 GMT

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.

Re: Updating column in table if matching values occur in another table

Haikuo — Wed, 28 Jan 2015 16:44:30 GMT

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;

Re: Updating column in table if matching values occur in another table

Haikuo — Wed, 28 Jan 2015 16:48:37 GMT

"Not" should be faster than IFN()?

Buy=not h.check();

Haikuo