topic Request for Help with SAS Dates Passing through PROC SQL in SAS Programming

Request for Help with SAS Dates Passing through PROC SQL

--- — Fri, 30 Oct 2020 02:43:28 GMT

Hi there,

I have a piece of code that works if I do this

%LET sd = '1Jul2020'd;

PROC SQL;
CREATE TABLE xxx AS SELECT DISTINCT
xxx
FROM
xxx
WHERE 
date BETWEEN &sd. AND INTNX('month', &sd., 3, 'same') - 1;
QUIT;

But if I do

DATA _NULL_;
start_date = MDY(%SUBSTR(&quarter., 6, 1) * 3 - 2, 1, %SUBSTR(&quarter., 1, 4));
CALL SYMPUT('start_date', start_date);
...
RUN;

And replace &sd. with &start_date., what passes through PROC SQL is a SAS date and returns incorrect/nil data - how do I turn my newly created start_date variable into something that's useable?

I've googled this and tried a few things and can't find anything that I can use unfortunately

Thanks for your help

Re: Request for Help with SAS Dates Passing through PROC SQL

SASKiwi — Fri, 30 Oct 2020 02:53:44 GMT

Try this:

DATA _NULL_;
start_date = MDY(input(SUBSTR("&quarter.", 6, 1),1.) * 3 - 2, 1, input(SUBSTR("&quarter.", 1, 4),4.));
CALL SYMPUT('start_date', start_date);
...
RUN;

If this doesn't work then you will need to post how you assign QUARTER.

Re: Request for Help with SAS Dates Passing through PROC SQL

--- — Fri, 30 Oct 2020 03:54:45 GMT

Thanks for your prompt response

I've forgotten about the quotes, thanks for pointing that out - however, adding them still gave me the same outcome

This is my quarter variable

%LET quarter = 2020Q3;

Ultimately I was able to generate a start_date = 1 July 2020 even with my old code but this is 22097 in SAS without applying format and it's attempting to pass 22097 into the PROC SQL where statement rather than '1Jul2020'd

I tried turning the date into a character and then add ''d to it but none seem to work - any other advice would be greatly appreciated

Re: Request for Help with SAS Dates Passing through PROC SQL

--- — Fri, 30 Oct 2020 05:24:58 GMT

Just played with the codes a bit more and found that this would work - what I hadn't tried was using double rather than single quotes... 😑 feels like banging my head against the wall

DATA _NULL_;
	date = MDY(SUBSTR("&quarter.", 6, 1) * 3, 1, SUBSTR("&quarter.", 1, 4));
	CALL SYMPUTX('date', PUT(date, DATE9.));
RUN;

PROC SQL;
	CREATE TABLE xxx AS SELECT DISTINCT
	    xxx
	FROM
		xxx
	WHERE 
		date BETWEEN "&date."d AND "&date."d;

QUIT;

Re: Request for Help with SAS Dates Passing through PROC SQL

SASKiwi — Fri, 30 Oct 2020 05:28:42 GMT

So you are expecting 2020Q3 to convert to a SAS date of 01 July 2020?

Re: Request for Help with SAS Dates Passing through PROC SQL

--- — Fri, 30 Oct 2020 05:32:05 GMT

Yes among other things - I think there might be a date format that can convert more elegantly but it worked for me 🙂

Re: Request for Help with SAS Dates Passing through PROC SQL

SASKiwi — Fri, 30 Oct 2020 21:32:36 GMT

@--- - This is a bit cleaner:

%let quarter = 2020Q3;

data test;                                     
   sasdate=input("&quarter",yyq6.);                           
   put sasdate date9.;              
run;