Convert Dates and Setting Date Limits

[Cheesiepoof05]

Hello, I'm new to SAS and working on a simple query using a PROC SQL statement.  I'm trying to do two things.  1. Convert the date column to a date like 20230101 (also interested in how to convert it to just year month like 202301).  2. I will also need to be able to limit to a certain date range like something like 

WHERE t1.Date > 20230101

AND t1.Date < 20230331

SAS is currently showing the date in the following format when running my query:  01AUG2017:00:00:00.000

SQL server management studio states that the date format is "datetime" and shows it in this format:  2020-07-07 00:00:00.000

This is my current statement.  But I'm looking to convert the date to YYYYMMDD and I also need to add in the ability to limit within a date range.

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_DATE AS
SELECT

t1.Event,
t1.Date
FROM FACS.Occurances t1
;
QUIT;

[PaigeMiller · Accepted solution]

You should NOT convert actual SAS dates to 20230101, as this makes it more difficult to work with. SAS does not recognize 20230101 as a date, even if humans do recognize it as a date. So work with SAS, not against it.

To work with SAS, and use actual SAS date values or SAS date/time values, your SQL snippet becomes

WHERE datepart(t1.Date) > '01JAN2023'D
AND datepart(t1.Date) < '31MAR2023'D 

Whatever else you need to do with dates can be done in similar fashion to the above, and by leaving the values as SAS date (or date/time), all logical and arithmetic operations with dates will work.

If you need to have dates that have a specific appearance in your output , such as 20230101, you do not convert the date to something else, you change its appearance by applying a format, such as YYMMDDN8.

[Cheesiepoof05]

Thank you, that works perfectly using the dates.  I have two follow up questions though.  I'm setting these dates up to be prompts and am now struggling to get it to accept the value '01JAN2022'D as the start_date prompt.  

AND datepart(t1.Date) >= (&Start_Date.)
AND datepart(t1.Date) <= '31MAR2022'D

Also, the original reason I wanted to break it down to year and month is because I would like to be able to group results by year month to see variances by each month.  How is that accomplished if I don't convert the date to something like YYYYMM?

[PaigeMiller]

---

@Cheesiepoof05 wrote:

Thank you, that works perfectly using the dates.  I have two follow up questions though.  I'm setting these dates up to be prompts and am now struggling to get it to accept the value '01JAN2022'D as the start_date prompt.  

AND datepart(t1.Date) >= (&Start_Date.)
AND datepart(t1.Date) <= '31MAR2022'D

Also, the original reason I wanted to break it down to year and month is because I would like to be able to group results by year month to see variances by each month.  How is that accomplished if I don't convert the date to something like YYYYMM?

---

%let start_date=%sysevalf('01JAN2022'd);

The grouping is what formats do. Assign the proper format to your date, and then almost all SAS procedures will group everything by the formatted date, i.e. all dates in January 2021 will be grouped together if the formatted date is 202301.

[Cheesiepoof05]

Thank you again, but I am not following the prompt piece.

This is the code where it was working to create my limit, but I have created a prompt so that when I kick off the query it pops up and I would enter whatever start date I want instead of 01JAN2022.

AND datepart(t1.Date) >= '01JAN2022'D
AND datepart(t1.Date) <= '31MAR2022'D

My prompt is called Start_Date and I updated the code to this:

AND datepart(t1.Date) >= (&Start_Date.)
AND datepart(t1.Date) <= '31MAR2022'D

You provided this but I'm not sure how to use it:

 %let start_date=%sysevalf('01JAN2022'd);

[PaigeMiller]

At or near the start of your program

%let start_date=%sysevalf('01JAN2022'd);

and then later in your program

AND datepart(t1.Date) >= &Start_Date
AND datepart(t1.Date) <= '31MAR2022'D

[Cheesiepoof05]

Ok, what am I doing wrong.  My code is below and I've tried entering variations when the prompt pops up of 01JAN2022 and '01JAN2022'.  I keep erroring out still.

PROC SQL;

 %let start_date=%sysevalf('01JAN2022'd);

   CREATE TABLE WORK.QUERY_FOR_DATE AS 

   SELECT

          t1.Event,  

          t1.Date,

      FROM FACS.Occurances t1

      WHERE datepart(t1.FillDate) >= &Start_Date.

      AND datepart(t1.FillDate) <= '30JAN2022'D

;

QUIT;

[Tom]

---

@Cheesiepoof05 wrote:

Thank you, that works perfectly using the dates.  I have two follow up questions though.  I'm setting these dates up to be prompts and am now struggling to get it to accept the value '01JAN2022'D as the start_date prompt.  

AND datepart(t1.Date) >= (&Start_Date.)
AND datepart(t1.Date) <= '31MAR2022'D

Also, the original reason I wanted to break it down to year and month is because I would like to be able to group results by year month to see variances by each month.  How is that accomplished if I don't convert the date to something like YYYYMM?

---

So check what string the prompting systems is setting into the macro variable START_DATE by adding a %PUT to the start of your program.

%put &=start_date ;

Once you see the types of STRINGS it is inserting into the macro variable then you can understand how to use the macro variable to generate valid code.

From previous discussions on this site about date prompts it should be setting the value to something like:

01JAN2022

In which case to use it to generate valid SAS code for a date literal you need add the quotes and the letter D.

"&start_date"d

Notice the use of double quotes instead of single quotes.  The macro processor will ignore strings enclosed in single quotes so you have to use double quotes.

[Cheesiepoof05]

Bingo!  I appreciate you jumping in and taking it down to my level.  Using the double quotes allowed it to follow the format Paige laid out before but using the prompt.  Thanks so much!

[ballardw]

Note that there are MANY display options for SAS date, time, or in this case DATETIME values.

In fact if SAS doesn't include one that appears the way you would like then Proc Format will likely allow you to create your custom appearance.

In the case of displaying Datetime values as YYYYMMDD then format you want would be B8601dn8. If you change the format of your variable to that you will see values displayed as desired.

OR, since there doesn't appear to actually be any time component to your values permanently extract the date portion with the Datepart format and assign  it the YYMMDN8. The N in that format means "no separator" as you want.

data want;
   set have;
   date = datepart(date);
   format date yymmddn8.;
run;

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.

Regardless of the display format assigned to a DATE value you use values in the "DDMONYYYY"D structure, where MON is the three letter abbreviation for the month name, to create date literal values for comparison. There are so many different "standard" date layouts that SAS chose the given one so that regardless of national rules can recognize the value as intended to be a date AND does not need conversion when you move code from someplace that prefers DDMMYYYY, MMDDYYYY or YYYYMMDD appearance for people.