Help using Base SAS procedures

Extracting the Count based on Conditions how to do it with proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

Extracting the Count based on Conditions how to do it with proc sql

/* 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
Solution
‎07-16-2016 02:16 AM
Super User
Posts: 10,023

Re: Extracting the Count based on Conditions how to do it with proc sql

Posted in reply to atul_desh
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


All Replies
Super User
Posts: 5,499

Re: Extracting the Count based on Conditions how to do it with proc sql

Posted in reply to atul_desh

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 )

Super User
Posts: 10,023

Re: Extracting the Count based on Conditions how to do it with proc sql

Posted in reply to atul_desh

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;
Contributor
Posts: 65

Re: Extracting the Count based on Conditions how to do it with proc sql

I want output like this : 

 

SAS Output

Obs custid s 1 2
a2
b1
Solution
‎07-16-2016 02:16 AM
Super User
Posts: 10,023

Re: Extracting the Count based on Conditions how to do it with proc sql

Posted in reply to atul_desh
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;

Contributor
Posts: 65

Re: Extracting the Count based on Conditions how to do it with proc sql

Thank you Xia Keshan !!! 

Spoiler
 
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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