SAS: Using PROC SQL to write a date variable into a SAS dataset

Question

I'm working on a piece of code in SAS designed to pull the last non-null value in a given column of time series data along with its corresponding date then insert them into a new dataset. SQL seems like by far the easiest way to do this, so that's what I've been working with.

The dataset I'm reading from is called rtchg1, imported from an .xlsx spreadsheet in earlier code. It's composed of a date variable and a bunch of time series variables.

data rtchg1;
set rtchg1;
where date between '1FEB1959'd and '1OCT1998'd;
run;

The table I'm writing to is Forecasts, created with some simple SQL:

PROC SQL ;
    CREATE TABLE Forecasts
    (Date date,      
     Forecast num);
run;    

I had previously formatted 'Date' more complexly and encountered the same issues. Let me know if I'm doing something wrong here though, any advice on how to improve my code is appreciated.

Lastly I've been developing the following macro to pull the data:

%macro scoreprep(readtable,datevar,writetable); 
%do i=1 %to 3;

    %let currentvar=%scan(&periods,&i);

    proc sql;
        select &datevar, &currentvar into :date1, :LEI
        from &readtable
        where &currentvar is not null
        having &datevar=max(&datevar);

        insert into &writetable (Date, Forecast)
            values ("&date1"d, &LEI);
    quit;

%end;
%mend;

%scoreprep(rtchg1,date,Forecasts); 

It only goes from 1 to 3 for now in order to test it without too much of a wait time, etc. Everything here seems to work perfectly EXCEPT for inserting the date variable into the table. When I removed the date variable and just input &LEI it wrote that to the Forecasts table without any issues. When I run the code as-is I get the following error:

ERROR: Invalid date/time/datetime constant "10/01/1968"d.

Not really sure where to go on from here, no matter where I try to convert the format of the macro variable nothing seems to work properly. Advice would be greatly appreciated. Also if you see anything you don't like in my code as-is, feel free to criticize. I do know easier ways to do this in Excel, but this way is more fun :)

Answer 1

Since you are pulling the values into a character string you need to tell SAS how to convert that character string back into a date. Be explicit in how you are doing the conversions. First in generating the macro variable and then in generating the VALUES() statement. So if Forecast is a number then this should work with only minimal loss of precision.

select put(&datevar,date9.) ,put(&currentvar,best32.) into :date1 ,:LEI
...
values ("&date1"d, &LEI)

But if it is a character variable then you might want to use this instead.

select put(&datevar,date9.), quote(&currentvar) into :date1,:LEI
...
values ("&date1"d, &LEI)

Also make sure that the variable you are using as the source for your DATE actually has DATE values and not DATETIME values. If is has DATETIME values then you could use the DTDATE format to generate a macro variable in the right format. Or use the datepart() function to extract just the date value.

Note that it doesn't make sense to put data into macro variables just so you can later put it back into data. So you could use PROC APPEND.

%macro scoreprep(readtable,datevar,writetable);
%local currentvar i ;
%do i=1 %to %sysfunc(countw(&periods));
  %let currentvar=%scan(&periods,&i);
proc sql;
  create table to_add as
    select &datevar as date 
         , &currentvar as forecast
    from &readtable
    where &currentvar is not null
    having &datevar=max(&datevar)
  ;
quit;
proc append data=to_add base=&writetable force ;
run;

%end;
%mend;

%scoreprep(rtchg1,date,Forecasts);

Or even just use SQL code to insert the result of the query.

insert into &writetable (date,forecast)
  select &datevar, &currentvar
    from &readtable
    where &currentvar is not null
    having &datevar=max(&datevar)
;

Answer 2

Try changing this line like so and see if that helps:

    select &datevar format=date9., &currentvar into :date1, :LEI