BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jpprovost
Quartz | Level 8

The type is DATE and the format is DATE9.

I really think that the DATE9 format is not liked by PROC SQL... Is there a way to diagnose this out?

I don't know where I can make an error.

The dataset contains a DATE value in DATE9 format.

I use a criteria which is exactly in a DATE9 format.

I compare those two together and it doesn't work.

 

I will continue seeking for a solution.

mkeintz
PROC Star

It doesn't matter to SQL (or any SAS DATA step or proc) what the format of a date variable is, because the internal numeric value of the variable is the same regardless of format.   You problem is apparently how to use a macrovar to express a date literal value for use in PROC SQL.  That is why I recommend:

 

%let date1=31DEC2018;

 

proc sql ....;

   ... where date_table <="&date1"d;

 

 

regards,

Mark

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Tom
Super User Tom
Super User

So first see what values you actually have in the data.

For example you could pull out the unformatted min and max values and compare them to today's date as a DATE (number of days) and a DATETIME (number of seconds).

proc sql noprint;
select min(datetable) format=best32.
    , max(datetable) format=best32.
  into :min_date trimmed , :max_date trimmed
from tablesource
;
quit;
%put Date range = &min_date to &max_date ;
%put DATE     = %sysfunc(today());
%put DATETIME = %sysfunc(datetime());
jpprovost
Quartz | Level 8

@Tom wrote:

So first see what values you actually have in the data.

For example you could pull out the unformatted min and max values and compare them to today's date as a DATE (number of days) and a DATETIME (number of seconds).

proc sql noprint;
select min(datetable) format=best32.
    , max(datetable) format=best32.
  into :min_date trimmed , :max_date trimmed
from tablesource
;
quit;
%put Date range = &min_date to &max_date ;
%put DATE     = %sysfunc(today());
%put DATETIME = %sysfunc(datetime());

 

I tried it and here's the output from the log :

 

Date range = 21556 to 21822
DATE = 21823
DATETIME = 1885557787.48419

 

Sorry for the delay, it took 10 minutes to compute this.

Tom
Super User Tom
Super User

So it sounds like your original code is working.  You do NOT have any date values that are from before 2019.

43    data test;
44     input date;
45     put date= date9. +1 date= comma7.;
46    cards;

date=07JAN2019  date=21,556
date=30SEP2019  date=21,822
jpprovost
Quartz | Level 8

Everything I tried in this thread would work if this was not the fact that the date I'm looking for was not into the dataset. I will learn from it (aka maxim #3) for sure.

 

Thanks everyone. You are a great community for sure.

jpprovost
Quartz | Level 8

@Kurt_Bremser wrote:

Maxim 3: Know Your Data.

Run proc contents on tablesource and look at the type and format of date_table. From that you can infer its content.


Hi @Kurt_Bremser,

 

Thanks for the reply and help. I try my best to "know my data", trust me 😉

 

However, I run a PROC CONTENTS on my dataset and the date is stocked as :

 

Type = Num

Len = 8

Format = DATE9.

Informat = DATE9.

 

Thanks,

Kurt_Bremser
Super User

@jpprovost wrote:

@Kurt_Bremser wrote:

Maxim 3: Know Your Data.

Run proc contents on tablesource and look at the type and format of date_table. From that you can infer its content.


Hi @Kurt_Bremser,

 

Thanks for the reply and help. I try my best to "know my data", trust me 😉

 

However, I run a PROC CONTENTS on my dataset and the date is stocked as :

 

Type = Num

Len = 8

Format = DATE9.

Informat = DATE9.

 

Thanks,


So the next step of "Know Your Data" is to get a grip of the contents, which you did by running @Tom's code, and that gave you the answer. Characterization of data is important when one runs into such issues.

jpprovost
Quartz | Level 8
I totally agree with you. Thank you for your time and help. Since I'm relatively new with SAS, I'm very grateful that such a great community like this exist.

Thanks again!
mkeintz
PROC Star

I would recommend

 

%let date1=31dec2019; 

 

and inside your program just use:

 

WHERE DATE_TABLE <= "&date1"d ;

 

 

Using a macro ready-made for date-literal usage, you have no need for %sysfunc.

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
jpprovost
Quartz | Level 8

Still do not works. I think we have a problem with the field in our dataset.

I will try other thing.

 

Reeza
Super User

@jpprovost wrote:

Still do not works. I think we have a problem with the field in our dataset.

I will try other thing.

 


My money is on your variable being a datetime not a date variable. If that's the case, change your WHERE condition to use DATEPART()

 

where datepart(dateVar) = macroVariableWhatever

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 27 replies
  • 24058 views
  • 9 likes
  • 8 in conversation