BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
atul_desh
Quartz | Level 8

/* 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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

5 REPLIES 5
Astounding
Opal | Level 21

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 )

Ksharp
Super User

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;
atul_desh
Quartz | Level 8

I want output like this : 

 

SAS Output

Obs custid s 1 2
a2
b1
Ksharp
Super User
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;

atul_desh
Quartz | Level 8

Thank you Xia Keshan !!! 

Spoiler
 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

What is Bayesian Analysis?

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1068 views
  • 0 likes
  • 3 in conversation