Request for Help with SAS Dates Passing through PROC SQL

[---]

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

[SASKiwi]

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. 

[---]

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 

[SASKiwi]

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

[---]

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

[--- · Accepted solution]

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;

[SASKiwi]

@---  - This is a bit cleaner:

%let quarter = 2020Q3;

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