/* Variable 'S' will give us counts of transaction which happend in last 24 Hrs. from their last purchase*/
/*But how can I get same out with proc sql ??*/
options datestyle =mdy;
data ds1;
input trndt anydtdtm. trnid custid $;
format trndt datetime18.;
datalines;
7/12/2016 04:00:00 005 a
7/11/2016 01:00:00 004 a
7/12/2016 01:00:00 003 a
5/11/2016 04:00:00 004 b
5/12/2016 05:00:00 003 b
;
run;
proc sort data = ds1;
by custid descending trndt ;
run;
data ds2 (keep=s keep=custid);
set ds1;
by custid;
format y datetime18.;
retain y s;
if first.custid then Y=trndt ;
else if y=y;
x=y-trndt; if x=0 then s=1;else if x <=24*60*60 then s+1;
if last.custid then output;
proc print data =ds2;
run;
So you want the max value of trndt for each custid ? options datestyle =mdy; data ds1; input trndt & anydtdtm. trnid custid $; format trndt datetime18.; datalines; 7/12/2016 04:00:00 005 a 7/11/2016 01:00:00 004 a 7/12/2016 01:00:00 003 a 5/11/2016 04:00:00 004 b 5/12/2016 05:00:00 003 b ; run; proc sql; select *,(select count(*) as s from ds1 where custid=a.custid and trndt between intnx('dtday',a.trndt,-1,'s') and a.trndt) as s from (select * from ds1 group by custid having trndt=max(trndt)) as a; quit;
I have to assume that your SQL knowledge exceeds mine, or you wouldn't be asking. So I can give you some pieces of the solution.
Within your SELECT statement:
group by custid
count(*) where ( (max(trndt) - trndt) <= 60 * 60 * 24 )
You didn't post the output yet.
options datestyle =mdy;
data ds1;
input trndt & anydtdtm. trnid custid $;
format trndt datetime18.;
datalines;
7/12/2016 04:00:00 005 a
7/11/2016 01:00:00 004 a
7/12/2016 01:00:00 003 a
5/11/2016 04:00:00 004 b
5/12/2016 05:00:00 003 b
;
run;
proc sql;
select *,(select count(*) as s from ds1 where custid=a.custid
and trndt between intnx('dtday',a.trndt,-1,'s') and a.trndt) as s
from ds1 as a;
quit;
I want output like this :
SAS Output
a | 2 |
b | 1 |
So you want the max value of trndt for each custid ? options datestyle =mdy; data ds1; input trndt & anydtdtm. trnid custid $; format trndt datetime18.; datalines; 7/12/2016 04:00:00 005 a 7/11/2016 01:00:00 004 a 7/12/2016 01:00:00 003 a 5/11/2016 04:00:00 004 b 5/12/2016 05:00:00 003 b ; run; proc sql; select *,(select count(*) as s from ds1 where custid=a.custid and trndt between intnx('dtday',a.trndt,-1,'s') and a.trndt) as s from (select * from ds1 group by custid having trndt=max(trndt)) as a; quit;
Thank you Xia Keshan !!!
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.