CODEWITHSUNDEEP
sas
surge3333
surge3333

Reputation: 195

Date comparison using PROC SQL within SAS

I'm using PROC SQL within SAS and trying to get a count where the current month is equal to the month on a date field I'm reading. the format of the input date is - mmddyy10.

This is a sample of what I'm trying –

data test;                                                          
  input job $ lastrun;                                              
  DateNew = datejul(lastrun);                                       
  Format datenew mmddyy10.;                                         
  datalines;                                                        
joba 19300                                                          
jobb 19200                                                          
jobc 19303                                                          
jobx 19288                                                          
run;                                                                

proc print; run;                                                    

proc sql;                                                           
select                                                              
    count(job) AS cnt_LastMonth                                     
from test                                                           
where datepart(datenew) = intnx('month', today(), -1, 'same');      
quit;

In this example I'm expecting the cnt_LastMonth to return 3, however it returns 0.

Upvotes: 0

Views: 3556

Answers (2)

Schilker
Schilker

Reputation: 505

proc sql;                                                           
  select count(job) AS cnt_LastMonth                                     
  from test                                                           
  where month(DateNew)= 10;      
quit;

OR

proc sql;
SELECT count(A2.job) AS cnt_LastMonth                                           
    FROM (SELECT *,
                 MONTH(Date_Minus_1) as Month_filter,
                 MONTH(DateNew) as Month
          FROM(SELECT *,
                      intnx('Month',today(),-1,'s') as Date_Minus_1 format=mmddyy10. 
               FROM test) A1)A2
Where A2.Month =A2.Month_filter; 
Run;

Upvotes: 0

Llex
Llex

Reputation: 1770

You can't calculate datepart from date variable, only from datetime. And if you want to compare dates that belong to one month, don't ignore year value.

proc sql;
create table qert as 
select                                                              
    count(job) AS cnt_LastMonth
from test                                                           
where intnx('month', DateNew, 0, 'b') = intnx('month', today(), -1, 'b');
/*Increments both dates to the month's begin 
Instead of it you can try to use:
where month(DateNew) = month(today())-1 and year(DateNew)=year(today());
*/  

quit;

Upvotes: 1

Related Questions