BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Putt_Ka
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

12 REPLIES 12
ballardw
Super User

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*/

Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Putt_Ka
Calcite | Level 5

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...

Reeza
Super User

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;

Aman4SAS
Obsidian | Level 7

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;

Tom
Super User Tom
Super User

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.

Aman4SAS
Obsidian | Level 7

Thanks. Problem solved.

Putt_Ka
Calcite | Level 5

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!

Tom
Super User Tom
Super User

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

Putt_Ka
Calcite | Level 5

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;

Peter_C
Rhodochrosite | Level 12

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)

Reeza
Super User

Try it Smiley Wink

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 4402 views
  • 7 likes
  • 7 in conversation