topic Re: How can PROC SQL return only results from the previous month in SAS Enterprise Guide

How can PROC SQL return only results from the previous month

Boswser — Fri, 15 Oct 2021 15:08:58 GMT

Hi folks! I have a run that returns a column in datetime format like this:

01SEP2021
01AUG2021
01JUL2021

I’m trying to write a dynamic WHERE statement in Proc SQL that will only return the last month’s results. What I am trying is this:

SELECT *
FROM table
WHERE date_column = intnx(‘month’, today(), -1, ‘same’);

It’s not giving me an error, but it is returning no results. It is currently October, so I was expecting it to return only the rows with 01SEP2021 as a value (and I am certain the results have SEP values).

I am not sure where I am going wrong here and appreciate any advice. Thank you!

Re: How can PROC SQL return only results from the previous month

PaigeMiller — Fri, 15 Oct 2021 15:11:44 GMT

I'll take a guess that the values 01SEP2021 are not numeric (they are not datetime as you said, they are possibly dates if they are numeric).

If that's not it, then we would need to see a portion of your data as a SAS data step (instructions) and the full SQL code.

Re: How can PROC SQL return only results from the previous month

Boswser — Fri, 15 Oct 2021 15:15:55 GMT

Thanks, maybe that is the issue.

So earlier in the run, the dates are pulled from the DB, and converted into text because they are transposed with other data. Eventually they ended up on their own column as Character values.

I assumed any kind of date function would not work with character values, so I converted them to a date value using this:

input(date_column, anydtdtm.) as date_column format=dtdate9.

However, I can undo this if there is a better way to get the previous month from a column constructed of character values like SEP2021, AUG2021, etc.

Re: How can PROC SQL return only results from the previous month

PaigeMiller — Fri, 15 Oct 2021 15:18:41 GMT

Always best to handle dates as numeric. However, I don't think you have explained enough for me to advise actual code. And, as I requested, it would be helpful if you provided a portion of your data in the requested format (and not in any other format).

Re: How can PROC SQL return only results from the previous month

Reeza — Fri, 15 Oct 2021 15:22:47 GMT

where date=put(intnx('month', today(), -1, 'B'), monyy5.);

https://stackoverflow.com/questions/69576466/where-statement-in-sas-eg-for-getting-the-prior-month-of-a-dataset?noredirect=1#comment122997280_69576466

Re: How can PROC SQL return only results from the previous month

Kurt_Bremser — Fri, 15 Oct 2021 15:37:45 GMT

If you use this

WHERE date_column = intnx(‘month’, today(), -1, ‘same’);

today (2021-10-15), it will look for date_column values with a date of 2021-09-15, and not 2021-09-01. If you want the first day of the previous month, use "b" as the fourth parameter of the INTNX function.

Re: How can PROC SQL return only results from the previous month

Boswser — Fri, 15 Oct 2021 15:45:07 GMT

Thank you Reeza. I did not see your reply. I tried this where statement instead of the one in the OP, but received an error for the expression with = having components of different data types. I must have converted the character values poorly. Should I keep the dates as character values, or should they be converted some other way? Originally, they are character values reading SEP2021 AUG2021 etc.

Re: How can PROC SQL return only results from the previous month

Boswser — Fri, 15 Oct 2021 15:45:52 GMT

Thank you Kurt! I tried using B and unfortunately got the same result, which is no results returned. There must be a problem with the data type.

Re: How can PROC SQL return only results from the previous month

Reeza — Fri, 15 Oct 2021 15:47:56 GMT

That code is if for your character variable so it converts the comparison date to character as well.

Re: How can PROC SQL return only results from the previous month

Boswser — Fri, 15 Oct 2021 15:55:31 GMT

Thank you. I went back to a previous step in the project to work with the date_column as a character value, prior to its conversion. I kept it as a character and inserted that where statement. Unfortunately it also returned no results.

In the previous iteration of the column, where the dates are character values, they do not include the day. So they are are SEP2021 and not 01SEP2021.

Can them being MMMYYYY affect how the where statement needs to be constructed?

Re: How can PROC SQL return only results from the previous month

Kurt_Bremser — Fri, 15 Oct 2021 16:00:43 GMT

A date value is a date value, regardless of the format applied.

But we MUST now know what we are dealing with. Run PROC CONTENTS on your dataset, and post the line from the output that describes your date column.

If you have the MONYY format in use, you will not see the actual dates; change the format to yymmdd10. and see what you get.

Since you mentioned a datetime once in your initial post, also make sure that you do not have fractions involved; Use the FLOOR function to convert a possible date with fractions to an integer.

Re: How can PROC SQL return only results from the previous month

Boswser — Fri, 15 Oct 2021 16:10:22 GMT

Thank you Kurt. I am pretty new to this!

PROC CONTENTS on the earlier dataset (where the date is a character value and reads SEP2021) provided this on date_column:

Type: Char
Len: 204
Format, Informat, and Label are blank.

On the data where I converted it into (what I assumed was) a date format (01SEP2021), it reads:

Type: Num
Len: 8
Format: DTDATE9.
Informat and Label are blank.

Re: How can PROC SQL return only results from the previous month

Kurt_Bremser — Fri, 15 Oct 2021 16:16:03 GMT

So it IS a datetime value, not a date. You need to use DATEPART to extract the date from it:

data have;
input date_column datetime19.;
format date_column dtdate9.;
datalines;
01sep2021:01:02:03
01oct2021:04:05:06
;

proc contents data=have;
run;

proc sql;
select *
from have
where datepart(date_column) = intnx('month',today(),-1,'b');
quit;

Partial result:

#	Variable	Typ	Länge	Ausg.Format
1	date_column	Num	8	DTDATE9.

date_column
01SEP2021

Re: How can PROC SQL return only results from the previous month

Boswser — Fri, 15 Oct 2021 16:54:06 GMT

This did it! Thank you so much for your time and help, the data types were really throwing me for a loop. I truly appreciate it!

Re: How can PROC SQL return only results from the previous month

Kurt_Bremser — Fri, 15 Oct 2021 17:56:08 GMT

Maxim 3 says Know Your Data, and there is a reason why it is so high up on the list 😉

Being fluent in data types, especially in the way SAS deals with dates and types, is a must.