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 :)

Tom · Accepted Answer · 2017-03-27T14:58:38.363

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

Interesting! Is this way more time efficient than the method I was using? — Josh Kraushaar, Mar 27 '17 at 13:49
Ignore previous (deleted comment), this works perfectly! I was looking at the wrong table haha. — Josh Kraushaar, Mar 27 '17 at 14:11
Big difference is in the accuracy. If you move data into macro variables then you are converting it to text. In addition to the confusion that the formatting can cause it can lose some precision in the numbers. SAS stores numbers as IEEE floating point values. — Tom, Mar 27 '17 at 14:37
Added code to answer your original question of how to make the data -> macro variable -> data round trip work. — Tom, Mar 27 '17 at 14:44
In terms of efficiency you might want to format your data in a way so that a single proc can generate all of the data rows you want in a single pass. Most likely you could do this using PROC SUMMARY if your data was in the right format. — Tom, Mar 27 '17 at 15:06
I'll try that out, thanks for your help you are a real lifesaver. — Josh Kraushaar, Mar 27 '17 at 20:46

score 0 · Answer 2 · answered Mar 27 '17 at 13:07

0

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

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

answered Mar 27 '17 at 13:07

user667489

9,501
2
24
35

I tried that and got a slightly different issue, the kind I was seeing before I tried converting &date1 to a date variable at the end of the code: > ERROR 202-322: The option or parameter is not recognized and will be ignored. – Josh Kraushaar Mar 27 '17 at 13:52
Does this query ever return more than one row from the table? If not then this should work, otherwise you would need to modify the query. – user667489 Mar 27 '17 at 14:36
It only ever returns one row, it just seems to refuse to recognize &datevar as a datevariable when attempting to insert it into the table. &datevar is always directly pulled from a column of dates so this is odd to me. – Josh Kraushaar Mar 27 '17 at 14:37

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

2 Answers2