topic Re: SAS Query to get past 1month's date from current date in New SAS User

SAS Query to get past 1month's date from current date

jyothiN — Sat, 29 Aug 2020 01:02:42 GMT

Could you please help me on the below query where i m trying to retreive data for past 1 month from current date.

query used:

proc sql noprint;
create table test1 as Select distinct quote_number(alphnumeric),
effective, TRANSACTIONDATE from Libname.table
where datepart (TRANSACTIONDATE) < intnx('month',today(),-1)
and quotenumber like 'Q%'
order by transactiondate desc;
Quit;

sample result set of the data retrieved:

QCAH3107000623 27OCT2018:00:00:00.000000 24OCT2018:21:39:25.228000

Actually i need past 6-month's data but first trying out to get with 1 month and to apply it for 6 months

This gives me result of 22 million.

Kindly help!

Re: SAS Query to get past 1month's date from current date

SASKiwi — Sat, 29 Aug 2020 01:40:36 GMT

What does "past 1 month from current date" mean? If today is 29 Aug 2020 does that mean you want 30 Jul 2020 to 29 Aug 2020 inclusive or 01 Jul 2020 to 31 Jul 2020 inclusive or some other range?

Re: SAS Query to get past 1month's date from current date

jyothiN — Sat, 29 Aug 2020 01:47:13 GMT

Thank you for quick response!

I need data date range from 30 Jul 2020 to 29 Aug 2020 inclusive.

Thanks

Re: SAS Query to get past 1month's date from current date

mkeintz — Sat, 29 Aug 2020 03:47:45 GMT

A couple comments. It's still August 28 here, so over here you would want

where datepart (TRANSACTIONDATE) >= "29jul2020"d

Notice it's >=, not <, since you apparently want the most recent month.

But why bother applying the datepart function to every observation, when you can do the equivalent test for a datetime cutoff. I.e., the condition could be modified to:

where TRANSACTIONDATE  >= "29jul2020:00:00:00"dt

So how to generate the 29jul2020:00:00:00? You could put this in the where clause:

where TRANSACTIONDATE >= intnx(dtmonth,intnx('dtday',datetime(),1),-1,'same')

The internal INTNX takes the current datetime and adds 1 day (unit DTDAY) to it, and by default aligns the result to the beginning of the day, i.e. 29AUG2020:00:00:00. Then the external INTNX goes back 1 month (minus 1 DTMONTH), from that value, but is told to align it to the same day-of-month (rather than defaulting to the beginning of the month). So it generates the datetime for 29JUL2020:00:00:00.

But wait, there's more. Instead of telling SQL to calculate these nested functions once per observation to repeatedly generate a constant, you can use macro language to calculate the constant just once, and use the resulting macrovar in the sql, as in:

%let midnight_tonight=%sysfunc(intnx(dtday,%sysfunc(datetime()),1));
%let midnight_one_month_prior=%sysfunc(putn(%sysfunc(intnx(dtmonth,&midnight_tonight,-1,same)),datetime20.));
%put &=midnight_one_month_prior;

proc sql noprint;
  create table test1 as Select distinct quote_number(alphnumeric),
  effective, TRANSACTIONDATE 
  from Libname.table 
  where TRANSACTIONDATE >= "&midnight_one_month_prior"dt
 and quotenumber like 'Q%'
  order by transactiondate desc;
Quit;

So this provides the correct filter, and minimizes the computing burden while doing it.

Re: SAS Query to get past 1month's date from current date

jyothiN — Mon, 31 Aug 2020 06:05:29 GMT

Hi, Greetings!

Thank you so much!

It worked and got the desired results.

Just a follow-up question - in case if i want to change to get prior 6 months data - i just need to change the '-1' to '-6' in the below query you had provided?

Thanks!