how to create a proportion or calculation in proc sql after a condition

[mramzy89]

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?

[sbxkoenk]

Hello,

Your PROC SQL's are not entirely clear to me and contain some errors.

Fortunately you mentioned the ultimate aim of your program, being the proportion of ALL customers claiming their reward within 10 days (and over 5 days) after receiving it.

I got rid of the PROC SQL's and solved it with a data step:

The PROC FREQ gives the requested proportion.

Here it is:

data abc;
 merge contacts reward;
 by Person_ID;
 if Reward_Received_Date and Reward_Used_Date then
  time_elapsed=INTCK('DAY',Reward_Received_Date,Reward_Used_Date);
 else
  time_elapsed=.;
 if 5 <= time_elapsed <= 10 then condition=1; else condition=0;
run;

proc sort data=abc;
 by Person_ID descending condition;
run;

data def;
 set abc;
 by Person_ID descending condition;
 if first.Person_ID then output;
run;

PROC FREQ data=def;
 tables condition;
run;

Good luck,

Koen