topic Re: Convert Text Date in a Proc SQL Where in SAS Programming

Convert Text Date in a Proc SQL Where

Rebecca_K — Fri, 01 Nov 2024 19:54:28 GMT

Hello! Still fairly new to SAS and having a hard time with text/date conversions. I have a Text formatted date (coming from a table that has text formatted column) and want to compare it to an actual date. Here's a sample mock-up that would help solve what I'm trying to do. Although it "runs" it doesn't actually filter the data to just the two dates that meet the requirement. (Member 2222 and 3333 should show, 1111 should be excluded) The "CheckDate" below needs to be in the MM/DD/YYYY format unfortunately.

DATA HAVE;
INPUT MEMBERID : $20. DOB : MMDDYY10.;
Format DOB MMDDYY10.;
CARDS;
1111 1/12/2001
1222 2/11/2003
3333 3/11/2003
;
run;

%let CheckDate = 2/2/2002;

Proc sql;
  select * from Have 
  where DOB > &CheckDate_new.
;quit;

Any help is appreciated!

Re: Convert Text Date in a Proc SQL Where

Tom — Fri, 01 Nov 2024 20:10:51 GMT

Not sure I understand your description, so let's just examine your CODE and explain what you did wrong there.

You created a NUMERIC variable named DOB with DATE values. You attached the MMDDYY format with a display width of 10 characters to it.

You then created a macro variable with the string 2/2/2002 and used it to generate a SAS statement that will look like this:

where DOB > 2/2/2002

So to the SAS language that is comparing the numbers in the DOB variable to the number that results from dividing 2 by 2 and then again by 2002. Will be something less than 1. Since the way SAS stores dates is by the number of days since 01JAN1960 you are asking for any DOB that is after 01JAN1960.

Instead you wanted to look for date values larger then February second of 2002 you would wnat to generate a SAS statement like:

where DOB > '02FEB2002'd

So either set your macro variable to have that string:

%let CheckDate = '02FEB2002'd ;

Or set it to the actual number that SAS uses to represent that date. Which is 15,373 :

1    data _null_;
2      check = '02FEB2002'd ;
3      put check= / check= comma. / check=mmddyy10. / check=date9.;
4    run;

check=15373
check=15,373
check=02/02/2002
check=02FEB2002

Re: Convert Text Date in a Proc SQL Where

PaigeMiller — Fri, 01 Nov 2024 20:24:37 GMT

%let CheckDate = 2/2/2002;

&checkdate is a text string with slashes in it, not a date. Dates cannot be equal to text strings.

To make a macro variables that has the proper values to compare to a date value, here are two methods

/* Method 1 -- The MDY Function */
%let checkdate = %sysfunc(mdy(2,2,2002));
/* Method 2 -- Use a date literal inside %SYSEVALF */
%let dt=%sysevalf('02FEB2002'd);

In Method 2, the date literal '02FEB2002'd must be in exactly that form (except FEB can be lower case), inside quotes and followed by the letter d. There are in reality a lot more than these two methods.

Your PROC SQL also has an error, there is no macro variable named &checkdate_new

Re: Convert Text Date in a Proc SQL Where

Kurt_Bremser — Fri, 01 Nov 2024 20:28:22 GMT

Make it a SAS date value:

%let CheckDate = %sysfunc(mdy(2,2,2002));

proc sql;
  select * from have 
  where DOB > &CheckDate.
;
quit;

Re: Convert Text Date in a Proc SQL Where

Rebecca_K — Fri, 01 Nov 2024 20:32:23 GMT

Thanks, all, sorry my description wasn't clear. Unfortunately, the CheckDate is coming from a table that has a TEXT format, and the dates will be in the "mm/dd/yyyy" format, so I won't be able to change it. What i can change is the WHERE clause (sorry, had "new" in there, edited it out)

where DOB > &CheckDate.

Can i cast one or the other variable to the same type, to make them compatible to each other for the compare? Thanks so much for your help!

Re: Convert Text Date in a Proc SQL Where

Kurt_Bremser — Fri, 01 Nov 2024 20:39:14 GMT

So you don't create the macro variable with a %LET, but in some other way from a dataset? Please show your real code.

Re: Convert Text Date in a Proc SQL Where

PaigeMiller — Fri, 01 Nov 2024 20:46:06 GMT

So you are saying you have a text string such as '02/02/2002' in a SAS data set, and you have to work with that? Am I understanding the situation properly now? So assume the variable in the data set is named DATE, with the text string value of '02/02/2002'. This little bit of creates macro variable CHECKDATE.

data _null_;
    set yourdataset;
    call symputx('checkdate',input(date,ddmmyy10.));
run;

Re: Convert Text Date in a Proc SQL Where

Tom — Fri, 01 Nov 2024 21:16:29 GMT

If you have strings in the MM/DD/YYYY style then you can use the MMDDYY INFORMAT to convert them into actual date values. So assuming the macro variable CHECKDATE has a valid SAS date value (as previously discussed) then just convert the string into a date for comparison.

where input(DOB,mmddyy10.) > &CheckDate.

Note: If you had stored the date strings in YMD order then you could have compared them directly since the character string would sort into chronological order. Assuming they all used the same delimiter and have two digits for month and day and four digits for year.

So you could do something like this.

where DOB > "2002/02/02"

It addition to the sorting issue note that using date strings in YMD order will prevent users from confusing December Tenth for October Twelfth.

Re: Convert Text Date in a Proc SQL Where

ballardw — Fri, 01 Nov 2024 21:58:39 GMT

@Rebecca_K wrote:

Hello! Still fairly new to SAS and having a hard time with text/date conversions. I have a Text formatted date (coming from a table that has text formatted column) and want to compare it to an actual date. Here's a sample mock-up that would help solve what I'm trying to do. Although it "runs" it doesn't actually filter the data to just the two dates that meet the requirement. (Member 2222 and 3333 should show, 1111 should be excluded) The "CheckDate" below needs to be in the MM/DD/YYYY format unfortunately.
DATA HAVE;
INPUT MEMBERID : $20. DOB : MMDDYY10.;
Format DOB MMDDYY10.;
CARDS;
1111 1/12/2001
1222 2/11/2003
3333 3/11/2003
;
run;

%let CheckDate = 2/2/2002;

Proc sql;
  select * from Have 
  where DOB > &CheckDate_new.
;quit;
Any help is appreciated!

First would be to use one name for the macro variable. Your code would, as far as I can see, been using an undefined macro variable, which means blank and should be throwing errors.

Compare date values to date values. The input function with the appropriate informat is the typical tool. But the INPUT function expects a text value so would need quotes:

%let CheckDate = 2/2/2002;

Proc sql;
  select * from Have 
  where DOB > input("&CheckDate.",mmddyy10.)
;quit;

Which with your example data yields:

MEMBERID	DOB
1222	02/11/2003
3333	03/11/2003

Supply the value in the manner that SAS expects date literals to by used: Quotes around a value in Date9. or Date7 appearance:

Proc sql;
  select * from Have 
  where DOB > "02FEB2002"D
   ;
quit;

The only time to worry about the Format of a value in comparisons is when you explicitly apply the format to the value for comparison. And since Put would be the method and creates character values any of the "less than" or "greater than" comparisons will almost always be a poor result unless the format is a YYMMDD order AND you make sure either both or no character values have the leading zeroes for month and day.

If you must use a macro variable at all I would go with something more like:

%let CheckDate = 02FEB2002;

Proc sql;
  select * from Have 
  where DOB > "&CheckDate."D
   ;
quit;

because regardless of the macro variable name a structure like "&macrovar"D should tell a moderately experienced program the value should 1) be a date and 2) looks like ddMONyyy or ddMONyy

Re: Convert Text Date in a Proc SQL Where

s_lassen — Wed, 06 Nov 2024 03:37:41 GMT

If I understand your second post correctly, what you have is not a SAS DATE numeric variable (as shown in your first post), but a text variable in the format "mm/dd/yyyy". If that is the case, you will have to convert the character values to SAS dates in the actual WHERE clause, something like

%let CheckDate = '01FEB2002'd;

Proc sql;
  select * from Have 
  where input(DOB,mmddyy10.) > &CheckDate
;quit;