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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.