- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
/* 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;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 )
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I want output like this :
SAS Output
a | 2 |
b | 1 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you Xia Keshan !!!