DATA Step, Macro, Functions and more

%let = if statement, or case?... confused...

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

%let = if statement, or case?... confused...

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


Accepted Solutions
Solution
‎08-11-2014 11:53 AM
Super User
Posts: 17,829

Re: %let = if statement, or case?... confused...

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


All Replies
Super User
Posts: 10,500

Re: %let = if statement, or case?... confused...

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

PROC Star
Posts: 1,231

Re: %let = if statement, or case?... confused...

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.

Occasional Contributor
Posts: 18

Re: %let = if statement, or case?... confused...

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

Solution
‎08-11-2014 11:53 AM
Super User
Posts: 17,829

Re: %let = if statement, or case?... confused...

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;

Super Contributor
Posts: 265

Re: %let = if statement, or case?... confused...

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;

Super User
Super User
Posts: 6,500

Re: %let = if statement, or case?... confused...

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.

Super Contributor
Posts: 265

Re: %let = if statement, or case?... confused...

Thanks. Problem solved.

Occasional Contributor
Posts: 18

Re: %let = if statement, or case?... confused...

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!

Super User
Super User
Posts: 6,500

Re: %let = if statement, or case?... confused...

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

Occasional Contributor
Posts: 18

Re: %let = if statement, or case?... confused...

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;

Valued Guide
Posts: 2,175

Re: %let = if statement, or case?... confused...

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)

Super User
Posts: 17,829

Re: %let = if statement, or case?... confused...

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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