topic Re: Check to see if this date is between a start date and end date and produce a result in new colum in SAS Programming

Check to see if this date is between a start date and end date and produce a result in new column

kmin87 — Fri, 07 May 2021 21:33:53 GMT

data test3;

Hello Nov 01 2016 is between Feb and DEc 2016 but it does not produce match in the pzone column. Is there something I am doing incorrectly? Any assistance would be greatly appreciated.

format dor yymmddn8.;

dor=161101;

length pzone $10;

if ( '01FEB16'd <=DOR <='31DEC16'd ) then do pzone="match";

end;

run;

Re: Check to see if this date is between a start date and end date and produce a result in new colum

PaigeMiller — Fri, 07 May 2021 21:39:20 GMT

dor=161101;

does not create a valid SAS date. Instead, use

dor='01NOV2016'd;

which does create a valid SAS date.

Re: Check to see if this date is between a start date and end date and produce a result in new colum

kmin87 — Fri, 07 May 2021 21:49:36 GMT

so the data set dor is in yymmddn8. format. I want to see if this DOR value is in between

One of the values has DOR that is 20161101 when I run the command, match does not appear as it should?

if ( '01FEB16'd <=DOR <='31DEC16'd )then do pzone="match";

Re: Check to see if this date is between a start date and end date and produce a result in new colum

kmin87 — Fri, 07 May 2021 21:51:26 GMT

Do I need to convert the DOR column that is in yymmddn8. format to another format to see if it can be compared to the 'DDMMYY'd format?

Re: Check to see if this date is between a start date and end date and produce a result in new colum

ballardw — Fri, 07 May 2021 22:00:55 GMT

@kmin87 wrote:

//

data test3;

Hello Nov 01 2016 is between Feb and DEc 2016 but it does not produce match in the pzone column. Is there something I am doing incorrectly? Any assistance would be greatly appreciated.

format dor yymmddn8.;

dor=161101;

length pzone $10;

//

if ( '01FEB16'd <=DOR <='31DEC16'd ) then do pzone="match";

end;

run;

What you are doing "incorrectly" is using a random number and treating it as a "date".

If you look at the formatted value for that example value of DOR you will see that it corresponds to dor=2401-01-29.

If 161101 is supposed to be in November to compare it with actual date values you have to make it into a date. One way looks like:

data _null_;
  dor=161101;
  actualdate = input(put(dor,6.),yymmdd6.);
  put actualdate= date9.;
run;

which will yield: actualdate=01NOV2016.

You can avoid such by reading your original data correctly. If you are using Proc Import it generally not "guess" that a value like that looks like a date, partially because it could be confused with 16Nov2021 (read as ddmmyy6). If you are reading the value with a data step then you should change the informat for the variable(s) to yymmdd or other that matches the content.

Re: Check to see if this date is between a start date and end date and produce a result in new colum

Reeza — Fri, 07 May 2021 22:07:33 GMT

It's not a matter of format, it's a matter of data understanding.

SAS stores dates as the number of days from January 1, 1960.

So the date November 01, 2016 is mathematically: 20,759.

SAS then uses the format, YYMMDD to display that as 2016-11-01

When you use:

dor=161101;

You're creating a date that's actually 161, 101 days from January 1, 1970 which is actually January 24, 2401.

dor="01Jan1960"d +161101;
format dor yymmddn8.;

To create a SAS date you must provide it as a date literal using either the DATE7 or DATE9 format, use a MDY() function or INPUT().

data test3;

dor=mdy(11, 1, 2016); output;
dor = input("161101", yymmdd6.); output;
dor = "01Nov2016"d; output;
format dor yymmddn8.;


run;

proc print data=test3;run;

Here's a great, but longer and in depth, reference for dates and times in SAS
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354

@kmin87 wrote:

//

data test3;

Hello Nov 01 2016 is between Feb and DEc 2016 but it does not produce match in the pzone column. Is there something I am doing incorrectly? Any assistance would be greatly appreciated.

format dor yymmddn8.;

dor=161101;

length pzone $10;

//

if ( '01FEB16'd <=DOR <='31DEC16'd ) then do pzone="match";

end;

run;

Also, note that the DO/END are not required in your code. This would be sufficient. It doesn't really make sense to have an assignment (pzone="match" as part of a DO). DO is usually used to loop or conditionally execute and you don't seem to be doing that. Better examples and explanations here

if ( '01FEB16'd <=DOR <='31DEC16'd ) then pzone="match";

Re: Check to see if this date is between a start date and end date and produce a result in new colum

kmin87 — Fri, 07 May 2021 22:11:06 GMT

OK so I am pulling this from a dataset that has a DOR column in YYYYMMDDn8. format

One of the values in that columns is 20161101.

if the DOR is between 01feb2016 and 31dec2016 then it would be a match

if DOR less than or equal to 31DEC16 then it will be match2

FOR EXAMPLE

The DOR colum/variable in the dataset has numerous rows with different dates in the YYYYMMDDn8. format one of them is 20161101. When I run it, it does not produce match

data test3;

set lib.test_data(keep dor);

length dummy $10;

if ( '01FEB16'd <='01NOV16'd <='31DEC16'd ) then dummy="match";

else if DOR <= '31DEC16'd then dummy="match2";

end;

run;

Re: Check to see if this date is between a start date and end date and produce a result in new colum

kmin87 — Fri, 07 May 2021 22:14:48 GMT

//

OK so I am pulling this from a dataset that has a DOR column in YYYYMMDDn8. format

One of the values in that columns is 20161101.

if the DOR is between 01feb2016 and 31dec2016 then it would be a match

if DOR less than or equal to 31DEC16 then it will be match2

FOR EXAMPLE

The DOR colum/variable in the dataset has numerous rows with different dates in the YYYYMMDDn8. format one of them is 20161101. When I run it, it does not produce match

data test3;

set lib.test_data(keep dor);

length dummy $10;

if ( '01FEB16'd <='01NOV16'd <='31DEC16'd ) then dummy="match";

else if DOR <= '31DEC16'd then dummy="match2";

end;

run;

//

Re: Check to see if this date is between a start date and end date and produce a result in new colum

Reeza — Fri, 07 May 2021 22:32:03 GMT

Your IF condition doesn't refer to your DOR variable, everything is hardcoded, is that what you intended? It will always be TRUE so your ELSE never gets evaluated.

Re: Check to see if this date is between a start date and end date and produce a result in new colum

ballardw — Fri, 07 May 2021 22:35:54 GMT

@kmin87 wrote:

//

OK so I am pulling this from a dataset that has a DOR column in YYYYMMDDn8. format

One of the values in that columns is 20161101.

if the DOR is between 01feb2016 and 31dec2016 then it would be a match

if DOR less than or equal to 31DEC16 then it will be match2

FOR EXAMPLE

The DOR colum/variable in the dataset has numerous rows with different dates in the YYYYMMDDn8. format one of them is 20161101. When I run it, it does not produce match

data test3;

set lib.test_data(keep dor);

length dummy $10;

if ( '01FEB16'd <='01NOV16'd <='31DEC16'd ) then dummy="match";

else if DOR <= '31DEC16'd then dummy="match2";

end;

run;

//

Did you run proc contents on the data set and that shows a format of YYMMDDn8. attached to the DOR variable?

The value you show in the first post is only 6 digits. YYMMDDn8 looks like 20161101 when formatting a value of 01NOV2016, not 161101. <=6 digits, not 8. So something inconsistent is going on.

Run this code:

data test3;
   set lib.test_data(keep dor);
run;

proc print data=test3;
   format dor yymmddn8.;
run;

Then show us some of the results from proc print.

Many people see numbers and assume they are a "date" with a given format. Quite often that is not the case.

Re: Check to see if this date is between a start date and end date and produce a result in new colum

kmin87 — Fri, 07 May 2021 23:25:27 GMT

I am not sure what you mean. I want to compare the values in the dor
variables/columns to a start and end date.

Re: Check to see if this date is between a start date and end date and produce a result in new colum

Patrick — Sat, 08 May 2021 03:35:50 GMT

Here your code fixed by replacing '01NOV16'd with variable DOR.

data test3;
  set lib.test_data(keep dor);
  length dummy $10;

  if ( '01FEB16'd <= DOR <='31DEC16'd ) then
    dummy="match";
  else if DOR <= '31DEC16'd then
    dummy="match2";
run;

SAS stores dates in numerical variables as the count of days since 1/1/1960. Such a number is of course not human readable as a date and that's why you also apply a date format to the variable so when you print the value it becomes human readable.

The format applied does not change the internal value stored (the count of days) and has no impact on any calculations or conditions. There the internal value (count of days) gets used.

The d in SAS syntax like '31DEC16'd let's you write a date in the form of a 'ddmonyy<yy>'d string. Using the d instructs SAS that this string is to be treated as a date and SAS will convert the string to a SAS date value prior of using it.

Have a look at below. May be that will make it clearer to you.

Printing the SAS Date value with format best32. shows you the value as the count of days since 1/1/1960. Also note that variable SASDateValue is of type numeric.

data _null_;
  length SASDateValue 8;
  SASDateValue='01FEB16'd;
  put 
    "SAS Date Value printed using different formats"
    / 'best32.  ' SASDateValue best32. -l
    / 'date9.   ' SASDateValue date9. -l
    / 'yymmddn. ' SASDateValue yymmddn. -l
    ;
run;

SAS Date Value printed using different formats
best32.  20485                           
date9.   01FEB2016
yymmddn. 20160201

In your initial post you pass to variable dor a value of 161101 - dor=161101;

For SAS this was the count of days since 1/1/1960 and though this date was clearly outside of the range you've tested for.

data _null_;
  length SASDateValue 8;
  SASDateValue=161101;
  put 
    "SAS Date Value printed using different formats"
    / 'best32.  ' SASDateValue best32. -l
    / 'date9.   ' SASDateValue date9. -l
    / 'yymmddn. ' SASDateValue yymmddn. -l
    ;
run;

SAS Date Value printed using different formats
best32.  161101                          
date9.   29JAN2401
yymmddn. 24010129

Re: Check to see if this date is between a start date and end date and produce a result in new colum

PaigeMiller — Sat, 08 May 2021 13:45:05 GMT

@kmin87 wrote:

The DOR colum/variable in the dataset has numerous rows with different dates in the YYYYMMDDn8. format one of them is 20161101. When I run it, it does not produce match
data test3;
set lib.test_data(keep dor);
length dummy $10;
if ( '01FEB16'd <='01NOV16'd <='31DEC16'd ) then dummy="match";
else if DOR <= '31DEC16'd then dummy="match2";
end;

run;

Now you have introduced what appears to be a logical error and programming errors.

if ( '01FEB16'd <='01NOV16'd <='31DEC16'd ) then dummy="match";

This does not use the variable DOR. So regardless of the value in DOR, the variable dummy will be assigned the value "match" from this statement.

else if DOR <= '31DEC16'd then dummy="match2";

and this statement will never execute, because the IF (which comes directly before the ELSE) is always true.

Maybe you mean to write:

if ( '01FEB16'd <=DOR <='31DEC16'd ) then dummy="match";

in which case the value of DOR is used. However, if the value of DOR is the integer 20161101, then SAS will not recognize this as a valid SAS date, and it will still not set the value of variable dummy to "match".

So, is DOR formatted somehow? (Use PROC CONTENTS to find out). If PROC CONTENTS says that DOR is unformatted, then you would need

if ( '01FEB16'd <=input(put(DOR,8.),yymmdd8.) <='31DEC16'd ) then dummy="match";

If PROC CONTENTS says that DOR is formatted as yymmdd8. then just placing DOR into the expression (which I did above) will work.

Lastly, you still have other programming or logical errors. Your set statement needs an equal sign

set lib.test_data(keep=dor);

and your logic is wrong too, if DOR is not between '01FEB16'd and '31DEC16'd, then the ELSE statement really will give you

else if DOR < '01FEB16'd then dummy="match2";

because when DOR is between those other dates you get dummy="match" and the ELSE will not execute. If DOR is after '31DEC16'd then variable dummy does not get assigned a value.

Please, @kmin87, from now on, 100% of the time, without fail, provide us with a representative portion of your actual data, following these instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/ This will lead to quicker and better answers, and save all of us a lot of time. I consider this mandatory, do not present us with SAS problems without also providing a portion of the data as I have described.

Re: Check to see if this date is between a start date and end date and produce a result in new colum

Reeza — Sun, 09 May 2021 01:44:12 GMT

if ( '01FEB16'd <='01NOV16'd <='31DEC16'd ) then dummy="match";

This is your code and test. It does not reference your variables, since there's no reference to DOR. So I assume this is not what you intended to do.

FYI - a very good way to ensure your code is logical when you start out is to comment every line with what you expect is happening. This helps you ensure that your code is working as expected.

@kmin87 wrote:
I am not sure what you mean. I want to compare the values in the dor
variables/columns to a start and end date.