Convert SAS DATE and use in a PROC SQL

Question

I'm having problems to DATEs in SAS Enterprise Guide 7.1 M4.

it's very very simple in SQL Server or VBA but in SAS is driving me crazy.

Problem: For some strange reason I'm unable to make a simple select. I tried many different forms of formating and convertions but any seems to work My Simple select returns no observations.

Description of T1.DT_DATE in proc contents Type: Num Len: 8 Format: DDMMYY10. Informat: DATETIME20.

%let DATE_EXAMPLE='01JAN2019'd;

data _null_;
      call symput ('CONVERTED_DATE',put(&DATE_EXAMPLE, ddmmyy10.));
run;

%put &CONVERTED_DATE;

PROC SQL;
    CREATE TABLE TEST_SELECT AS 
        SELECT * 
        FROM MY_SAMPLE_DATA as T1
        WHERE T1.DT_DATE = &CONVERTED_DATE
;QUIT;

I Finally understand. SAS date internally is a 5 lenght number, as you said, data literal is the way SAS comunicate. DDMMMYYYY'd or DATE9 But the main situation rely in this "D" in the last position, so I had figured out how to solve the problem cats("'", put(DATE_EXAMPLE, date9.), "'d") So this works in my new variable, passing to a proc sql no print : ) — Rodrigo Moraes

Kiran Kiran · Accepted Answer · 2019-01-28T19:46:26

Intially you are setting up the date properly but you are changing it to a different value that is not understood in where clause. See the resolutions of macrovariable for both macrovariables you have created

%put value of my earlier date value  is &DATE_EXAMPLE;
value of my earlier date value  is '01JAN2019'd
%put value of my current date value is &CONVERTED_DATE;
 value of my current date value is 01/01/2019

change your code to use date literal that is '01JAN2019'd then your code will work. 01/01/2019 value will not make sense in where clause.

PROC SQL;
CREATE TABLE TEST_SELECT AS 
    SELECT * 
    FROM MY_SAMPLE_DATA as T1
    WHERE T1.DT_DATE = &CONVERTED_DATE
;QUIT;

Convert SAS DATE and use in a PROC SQL

1 Answers