Greetings All!
I'm new to the SAS forums (first post), I'm coming over from the SQLServerCentral world, and am having a hard time getting used to the syntax with SAS. Here's my current dilemma:
I would like to create a variable "start_date" as the current date IF the current day of the month is equal to or greater than the 10th of the month; else I would like to set "start_date" to the first day of the current month.
TOUGH ONE!
Can I use a proc sql; case statement for something like this, or am I better off with if then else? Please help, I've been beating my head against the wall all friday and now this morning.
-Ben
I think you're looking to create a macro variable, the equivalent of a SQL variable that can be used in a further SQL step?
Here's a data step solution.
Some things that you'll need to look up are how SAS handles dates (numbers with formats attached), the intnx function used to align dates and the put function used to convert numbers to characters.
*Generates a numeric value for date, can be used directly in queries assuming your data has SAS dates;
data _null_;
if day(today())>10 then call symputx('start_date', today());
else call symputx('start_date', intnx('month', today(), 0, 'b'));
run;
*Generates a character value for date;
data _null_;
if day(today())>10 then call symputx('start_date_f', put(today(), date9.));
else call symputx('start_date_f', put(intnx('month', today(), 0, 'b'), date9.));
run;
%put &start_date;
%put &start_date_f;
Are your values SAS date values, if so relatively trivial. If they are strings they should probably be converted to SAS dates.
Do you have a "current date" variable or do you want the value from the day the program is run?
If the variables is a SAS DATE value and you want the current date to be the date the program is run then:
if day(today()) le 10 then Start_date = today();
else Start_date = mdy(1,month(today()),year(today())); /* there are other functions to increment dates but some learning curve gets involved*/
Welcome to the forum!
Could you show an example of the code you have tried?
Are you trying to create a macro variable &start_date or a data set variable (i.e. new column in a table)?
--Q.
It would be a Macro variable because I'm using it more than once in the code, and referencing it as &start_date. I first tried with proc sql code:
%let dayofthemonth = 8 *This could be any day of the month;
%let start_date =
proc sql;
IF &dayofthemonth >= 10 THEN date()
IF &dayofthemont < 10 THEN intnx('month',date(),-1,'E') *Last day of last month;
END;
So where someone could change the "dayofthemonth" easily and run the code...
I think you're looking to create a macro variable, the equivalent of a SQL variable that can be used in a further SQL step?
Here's a data step solution.
Some things that you'll need to look up are how SAS handles dates (numbers with formats attached), the intnx function used to align dates and the put function used to convert numbers to characters.
*Generates a numeric value for date, can be used directly in queries assuming your data has SAS dates;
data _null_;
if day(today())>10 then call symputx('start_date', today());
else call symputx('start_date', intnx('month', today(), 0, 'b'));
run;
*Generates a character value for date;
data _null_;
if day(today())>10 then call symputx('start_date_f', put(today(), date9.));
else call symputx('start_date_f', put(intnx('month', today(), 0, 'b'), date9.));
run;
%put &start_date;
%put &start_date_f;
Hi Reeza,
Can u please suggest me that why this code is not working .
NOT WORKING
%macro set_date();
%if %sysfunc(day(%sysfunc(today()))) ge 10 %then %do;
%let start_date= %sysfunc(today());
%end;
%else %do; %let start_date=%sysfunc(intnx(month,%sysfunc(today()),0,b)); %end;
%mend set_date;
%set_date;
%put &start_date;
WORKING
Data _null_;
if day(today()) ge 10 then
call symput("start_date",put(today(),date9.));
else call symput("start_date",put(intnx(month,today(),0,b),date9.));
run;
%put &start_date;
Two things wrong with the macro code.
1) If you have not defined the variable START_DATE before you call the macro then it will be local to the macro and so not exist after the macro finishes. You can fix this by just adding a %LET START_DATE=; before calling the macro.
2) You are not applying a format to the value when generating the macro variable. So instead of something like 12AUG2014 the value will by 19947 which is the actual number of days since 1/1/1960.
Thanks. Problem solved.
So in the code it's written in proc sql;
"WHERE tblcolumn BETWEEN &start_date and &end_date"
Does the date have to be written in the format of the column, or is there an implicit conversion into SAS date type?
Thanks for help all!
You can use a date literal by putting the data in DATE format ddMMMyyyy and wrapping it in quotes followed by the letter D.
Example: "11AUG2014"d
So in the examples above where macro variables have been created using the DATE9. format your where clause becomes.
WHERE tblcolumn BETWEEN "&start_date"d and "&end_date"d
A follow up question,
If I wanted to set 'end_date' to being 4 months before start date it would be something like this?:
data _null_;
CALL symput('end_date',intnx('month',&start_date, -4));
%put &end_date;
You might have noticed the use of date constants with a trailing d
Like
"&start_date"d
where I expect start_date holds something looking like a date
Adopt the convention in your code when storing dates in macro variables, store them in this DATE9 format
Generate these kinds of strings with a format in a put function in a data step statement like
CALL symput('end_date',put(intnx('month',"&start_date"d, -4), date9.)) ;
The corresponding macro syntax would be even simpler
%let end_date=%sysfunc(intnx(month,"&start_date"d,-4),date9);
There the only quoting indicates that a string should be trested as a date.
There is no need for the put function in that case because sysfunc() supports converting results of numeric functions with a format (date9 in that example)
Try it
Use the second version to check the actual value.
Also, look into the alignment option on intnx, so the date can align to the first/last/middle/same of the month.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.