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?
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
"Not" should be faster than IFN()?
Buy=not h.check();
Haikuo
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;
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;
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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.