Sysfunc Date9 with Select Into:

[DavidPhillips2]

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;

[Kurt_Bremser]

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;

[DavidPhillips2]

Kurt,

It looks like that code should work.  Oddly when I used your code I received the message ERROR: Date value out of range.

[Kurt_Bremser]

Check if the variable is really a SAS date, or if it just looks like one.

[DavidPhillips2]

today() is a function  (SAS and Oracle)

Date is being created in the select.

[Kurt_Bremser]

---

@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.

[Reeza]

If it's a database, it's like datetime

[Astounding]

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.

[Tom]

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.

[ballardw]

proc sql;
select put(year10_Date, date9) into :date separated by ''
from my_table
where curr_year_date = today();
quit;

should work.

[DavidPhillips2 · Accepted solution]

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();

[ScottBass]

%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;