data contacts;
infile datalines dlm=',' dsd truncover;
informat Person_ID best. Reward_Received_Date mmddyy10. Reward_ID best. Name $char15. TypeOfTransaction $10.;
format Reward_Received_Date mmddyy10.;
input Person_ID Reward_Received_Date Reward_ID Name TypeOfTransaction;
datalines;
1,12/20/20,0,Joe,Credit
1,9/30/20,135,Joe,Cash
2,1/5/21,168,Mitch,Gift Card
3,11/4/20,96,Mary,Cash
3,11/4/20,121,Mary,Cash
3,1/25/21,365,Mary,Credit
;
proc print data=contacts;run;
data reward;
infile datalines dlm=',' dsd truncover;
informat Person_ID best. Reward_Used_Date mmddyy10. Name $char15.;
format Reward_Used_Date mmddyy10.;
input Person_ID Reward_Used_Date Name;
datalines;
1,12/29/20,Joe
2,9/30/20,Mitch
2,1/7/21,Mitch
8,6/30/20,Mike
8,7/15/20,Mike
8,8/29/20,Mike
8,12/3/20,Mike
;
proc print data=reward;run;
PROC SQL; *left join;
Create Table Contacts_LeftJoin as
Select * from Contacts as a Left Join Reward as b
ON a.Person_ID=b.Person_ID;
Quit;
proc print data=Contacts_LeftJoin;run;
PROC SQL; *left join with condition;
Create Table Contacts_LeftJoinC as
Select * from Contacts as a Left Join Reward as b
ON a.Person_ID=b.Person_ID
where b.Reward_Used_Date between intnx('day',a.Reward_Received_Date,+5,'s') and
intnx('day',a.Reward_Received_Date,+10,'s')
;
Quit;
proc print data=Contacts_LeftJoinCR ;run;
proc sql;*left join with condition with proportion not correct yet;
create table final as
select Person_ID from Contacts_LeftJoin
union Person_ID from Contacts_LeftJoinC
where count(distinct Person_ID) as N_Persons
, count(distinct Contacts_LeftJoinC) as ET_Person
, calculated N_Persons % calculated ET_Person from Contacts_LeftJoinC
from final;
PROC SQL; *left join with condition with proportion not correct yet;
Create Table Contacts_LeftJoinC as
Select * from Contacts as a Left Join Testing as b
ON a.Person_ID=b.Person_ID
where b.Reward_Used_Date between intnx('day',a.Exposure_Date,+5,'s') and
intnx('day',a.Exposure_Date,+10,'s') and
count(distinct Person_ID) as N_Persons from Contacts and
, count(distinct Person_ID) as TR_Person from Rewards and
calculated N_Persons / calculated TR_Person from Contacts_LeftJoinC
order by b.Person_ID,b.Reward_Used_Date
;
Quit;
proc print data=Contacts_LeftJoinCR ;run So Im trying to make a proportion within proc sql after using a condition ( intnx ) . My goal is to create a proportion ( used '/' or % as divide sign) from person id counts from the original dataset( contacts) to the subset after the condition I created and give that a count ( i used distinct since some names are duplicated). In this case 1 person meets the condition out 4 people. I just cant code it for some reason. Research question is how many people used their loyalty rewards between day 5 and day 10 of receiving it? Should the 'count' statement be used before the condition ( intnx) part in this case. I am also open to more efficient coding than this Maybe I could have used the case statement instead of the intnx?
... View more