Date Formatting

[samface]

Hello SAS community,

I need help fixing my code, I am trying to writing a code that evaluates the performance of certains observations in my data over several monthly periods. I created a code that works but a bit tedious. To evaluate a perfomance over 12 months, I manually count 12 periods to find the ending date. As follow:

January - beginning_date=201701 - ending_date=201712

February - beginning_date=201702 - ending_date=201801

etc...

Here's my sample code below:

%beginning_date= 201701;
%ending_date= 201712;


PROC SQL;
	CREATE TABLE PERFORMANCE AS 
		SELECT t1.*,  
			t2.* 
		FROM DATA_X t1
			LEFT JOIN DATA_Y t2 ON(t1.A=t2.B)
				WHERE t2.DATE BETWEEN &beginning_date. AND &ending_date.;
QUIT;

I am now looking for a way to automate the counting process so that I can a performance over a 12, 18, 24 months. I used the code below but I am getting the following error message:

ERROR 22-322: Syntax error, expecting one of the following: !!,
*, **, +, -, /, AND, ||.

ERROR 76-322: Syntax error, statement will be ignored.

The DATE variable in my table is numeric not sure with values such as year/month 201701, 201702, etc..

Any idea or suggestions, thanks 🙂

%let beginning_date=201701 ; 

*Macro variable date;

DATA _null_;
DATE=&beginning_date.;
CALL SYMPUT("date_begin_perfo12", PUT(INTNX('month',input("&beginning_date.",yymmn6.),0,'end'),Date9.));
CALL SYMPUT("date_end_perfo12", PUT(INTNX('month',input("&beginning_date.",yymmn6.),+12,'end'),Date9.));
RUN;

%put &date_begin_perfo12.;
%put &date_end_perfo12.;

PROC SQL;
	CREATE TABLE PERFORMANCE AS 
		SELECT t1.*,  
			t2.* 
		FROM DATA_X t1
			LEFT JOIN DATA_Y t2 ON(t1.A=t2.B)
				WHERE t2.DATE BETWEEN &date_begin_perfo12. AND &date_end_perfo12.;
QUIT;

[novinosrin]

Try

WHERE t2.DATE BETWEEN "&date_begin_perfo12."d AND "&date_end_perfo12."d;

[PaigeMiller]

You would make your life sooooo much easier if you used actual SAS dates instead of a variable with values like 201701.

You also don't need to format the values before you use them in PROC SQL. Use formats only when a human has to view the results in an understandable form; PROC SQL doesn't care, it can use the actual date values, so there's no point in formatting them to be actual readable dates.

data _null_;
     begin_date=mdy(1,1,2017);
     end_date=intnx('month',begin_date,12,'s')-1;
     /* If you want more than a 12 month interval, replace 12 with the number of months you want */
     call symputx('begin_date',begin_date);
     call symputx('end_date',end_date);
run;

proc sql;
     ...
     where t2.date between &begin_date and &end_date;
quit;

Side note: I can't test this right now, but I do not remember if the BETWEEN operator will select the observation if the t2.date value is exactly equal to &begin_date or exactly equal to &end_date. In other words, I don't remember if BETWEEN uses greater than and less than; or greater than or equal to and less than or equal to. But you can test this and modify the code accordingly.