topic Re: Sysfunc Date9 with Select Into: in SAS Programming

Sysfunc Date9 with Select Into:

DavidPhillips2 — Thu, 27 Jun 2019 20:57:19 GMT

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;

Re: Sysfunc Date9 with Select Into:

Kurt_Bremser — Thu, 27 Jun 2019 20:59:34 GMT

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;

Re: Sysfunc Date9 with Select Into:

DavidPhillips2 — Thu, 27 Jun 2019 21:02:41 GMT

Kurt,

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

Re: Sysfunc Date9 with Select Into:

Kurt_Bremser — Thu, 27 Jun 2019 21:04:21 GMT

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

Re: Sysfunc Date9 with Select Into:

DavidPhillips2 — Thu, 27 Jun 2019 21:06:22 GMT

today() is a function (SAS and Oracle)

Date is being created in the select.

Re: Sysfunc Date9 with Select Into:

Tom — Thu, 27 Jun 2019 22:25:01 GMT

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.

Re: Sysfunc Date9 with Select Into:

ballardw — Thu, 27 Jun 2019 22:36:40 GMT

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

should work.

Re: Sysfunc Date9 with Select Into:

Kurt_Bremser — Thu, 27 Jun 2019 22:49:35 GMT

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

Re: Sysfunc Date9 with Select Into:

Reeza — Fri, 28 Jun 2019 00:00:52 GMT

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

Re: Sysfunc Date9 with Select Into:

ScottBass — Fri, 28 Jun 2019 00:42:54 GMT

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

Re: Sysfunc Date9 with Select Into:

Astounding — Fri, 28 Jun 2019 01:47:58 GMT

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.

Re: Sysfunc Date9 with Select Into:

DavidPhillips2 — Mon, 01 Jul 2019 13:43:08 GMT

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