How do you do the same thing as
date=%sysfunc(today(),date9);
With a select into: ?
proc sql;
select %sysfunc(year10_Date, date9) into :date separated by ''
from my_table
where curr_year_date = today();
quit;
I've tried syntax:
proc sql;
select year10_Date format=date9. into :date separated by ''
from mytable
where curr_year_date = today();
quit;
I have a date time so datepart() is needed.
select put(datepart(var1), date9.) into :Date separated by ' '
from mytable
where datepart(curr_year_date) = today();
If you need the dates for comparisons or selections, you don't need formats, just use the raw values.
Otherwise use SAS functions:
proc sql;
select put(year10_Date, date9.) into :date separated by ' '
from my_table
where curr_year_date = today();
quit;
Kurt,
It looks like that code should work. Oddly when I used your code I received the message ERROR: Date value out of range.
Check if the variable is really a SAS date, or if it just looks like one.
today() is a function (SAS and Oracle)
Date is being created in the select.
@DavidPhillips2 wrote:
today() is a function (SAS and Oracle)
Date is being created in the select.
I was talking about
year10_Date
This seems to not be a SAS date variable.
Find the maximum value that you are trying to select. If you divide that by 365, you will see approximately how many years into the future that number represents, as a date.
As others have hinted, it is possible you are pulling datetime values instead of date values. The SAS date scale only goes up to the year 20,000 so datetime values might be too large to be interpreted as a date.
The %SYSFUNC() macro function lets you call regular SAS function in macro code. So your first call
%sysfunc(today(),date9)
Is generating todays date and formatting it using DATE9 format. So that should result in a stream of characters that looks like
27JUN2019
You could use that in a %LET statement like:
%let date=%sysfunc(today(),date9);
But you couldn't use that in a regular SAS statement like:
date=%sysfunc(today(),date9);
because syntax like
date=27JUN2019;
is not valid. You need to either add quotes to make it a character literal or quotes and the letter D to make it a date literal.
Your first SQL statement is just gibberish because to the %SYSFUNC() macro function the string YEAR10_DATE is not a valid date value. It is looking for a value like "27JUN2019"d or 21727. And anyway it will just be a constant to SQL so even if you got the macro function to work you would generate the same value over and over again for every observation read.
Your second one looks a little better. In that one you are actually referencing a variable in the dataset. Not sure why you want to smush them together without any delimiter between them.
If it complained that the values didn't work for the format then the variable YEAR10_DATE does not have date values. It might have datetime values in which case you could use the DTDATE9. format instead.
proc sql; select put(year10_Date, date9) into :date separated by '' from my_table where curr_year_date = today(); quit;
should work.
I have a date time so datepart() is needed.
select put(datepart(var1), date9.) into :Date separated by ' '
from mytable
where datepart(curr_year_date) = today();
%let date1=%sysfunc(today());
%put &=date1; %* raw value ;
%let date2=%sysfunc(today(),date9.);
%put &=date2; %* formatted value ;
* use in a data step ;
data test2;
date1=&date1;
date2="&date2"d;
format _all_ date9.;
run;
* using proc sql ;
* (do you even need proc sql if %let + %sysfunc() meets your needs)? ;
proc sql noprint;
select today()
,today()
,today() format=date9.
,datetime() format=dtdate9.
into
:date3
,:date4 trimmed
,:date5
,:date6
from sashelp.class(obs=1);
quit;
%put date3=***&date3***;
%put date4=###&date4***;
%put &=date5;
%put &=date6;
* use in a data step ;
data test2;
date3=&date3;
date4=&date4;
date5="&date5"d;
date6="&date6"d;
format _all_ date9.;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: