DATA Step, Macro, Functions and more

How to change the value stored in the macro variable change dynamically based on a condition?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

How to change the value stored in the macro variable change dynamically based on a condition?

Hello,

here's what I am trying to accomplish:

 

%let annee=2018;

%let mois=02;

 

 

%macro bs(mois);

%if &mois = 01 %then %let dobs='31jan'&annee;

%if &mois = 02 %then %let dobs='28feb'&annee;

%if &mois = 03 %then %let dobs='31mar'&annee;

%if &mois = 04 %then %let dobs='30apr'&annee;

%if &mois = 05 %then %let dobs='31may'&annee;

%if &mois = 06 %then %let dobs='30jun'&annee;

%if &mois = 07 %then %let dobs='31jul'&annee;

%if &mois = 08 %then %let dobs='31aug'&annee;

%if &mois = 09 %then %let dobs='30sep'&annee;

%if &mois = 10 %then %let dobs='31oct'&annee;

%if &mois = 11 %then %let dobs='30nov'&annee;

%if &mois = 12 %then %let dobs='31dec'&annee;

%mend bs;

%put &dobs;

 

So basically, dobs should have the value "30feb2018"

 


Accepted Solutions
Solution
2 weeks ago
Super User
Super User
Posts: 9,599

Re: How to change the value stored in the macro variable change dynamically based on a condition?

Macro is always and only text.  You cannot apply datastep formats to something which does not exist in datastep language. This is one of the reasons i highly recommend you use Base SAS programming for data manipulation, and leave macro requirements out of it.

%let annee=2018;
%let mois=02;

data _null_;  
  call symputx('dobs', put(intnx('day',mdy(&mois,1,&annee),0)-1,date9.));
run;

%put &dobs;

View solution in original post


All Replies
PROC Star
Posts: 1,772

Re: How to change the value stored in the macro variable change dynamically based on a condition?

%let annee=2018;

%let mois=02;


%put &mois;
 

%macro bs;

 

%if &mois = 02 %then %let dobs="28feb&annee";


%put &dobs;
%mend bs;
%bs

Super User
Super User
Posts: 9,599

Re: How to change the value stored in the macro variable change dynamically based on a condition?

Why not:

%let annee=2018;
%let mois=02;

data _null_;
  call symputx('dobs',  intnx('day',mdy(&mois.,&annee,1),'beginning'));
run;

Its amazing what you can do with Base SAS - which is the actual programming language.

Occasional Contributor
Posts: 5

Re: How to change the value stored in the macro variable change dynamically based on a condition?

Thanks!

 

I tried the following:

 

%let annee=2018;

%let mois=02;

data _null_;

call symputx('dobs', intnx('day',mdy(&mois,1,&annee),0)-1);

format &dobs date9.;

run;

%put &dobs;

 

Output:

21215

 

How can I convert this to 28FEB2018

Super User
Posts: 10,214

Re: How to change the value stored in the macro variable change dynamically based on a condition?


@pkopersk wrote:

Thanks!

 

I tried the following:

 

%let annee=2018;

%let mois=02;

data _null_;

call symputx('dobs', intnx('day',mdy(&mois,1,&annee),0)-1);

format &dobs date9.;

run;

%put &dobs;

 

Output:

21215

 

How can I convert this to 28FEB2018


The question is, what for? If you need it for comparison or calculation purposes, the raw numerical value is ideal. If you need it for display anywhere, format it there. Note that the best (because standardized) format for a date is e8601da. (which is equivalent to yymmddd10.), as it conforms to the ISO 8601 standard.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 5

Re: How to change the value stored in the macro variable change dynamically based on a condition?

Posted in reply to KurtBremser
I have a huge table where each column has the name following this format:
DDMMMYYYY where MMM is a string representing the 3 first letters of each
month.

I want to have to replace the first two variables %annee and %mois and run
the program.

The macro variable %dobs has to dynamically adapt to my input and has to be
a macro variable because it is used everywhere in the program.
Super User
Posts: 10,214

Re: How to change the value stored in the macro variable change dynamically based on a condition?

[ Edited ]

@pkopersk wrote:
I have a huge table where each column has the name following this format:
DDMMMYYYY where MMM is a string representing the 3 first letters of each
month.

I want to have to replace the first two variables %annee and %mois and run
the program.

The macro variable %dobs has to dynamically adapt to my input and has to be
a macro variable because it is used everywhere in the program.

I see at least two violations of sound programming practice here:

  • first, using non-standard variable names starting with a digit. Although possible, those cause only unnecessary typing by forcing you to use the 'some garbage in here'n construct. Get rid of such things three days before yesterday.
  • second (and more severe), keeping data (dates) in structure (column names). Transpose to a long format upon import into SAS, and all handling of such data turns from PITA to trivial. That would also take care of the variable naming issue in one fell swoop. Since then dates are stored as SAS date values, formatting the raw value of the macro variable is a non-issue.

 

Edit: added the missing "severe".

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 5

Re: How to change the value stored in the macro variable change dynamically based on a condition?

Posted in reply to KurtBremser
I am dealing with legacy code and cannot afford to change the whole table
naming etc. But I will keep your suggestions in mind when doing my own
tables. Thank you
Super User
Super User
Posts: 9,599

Re: How to change the value stored in the macro variable change dynamically based on a condition?

Yes, what we have here is a classic case of bad data modelling using Excel thinking.  You have put "data" as column names, and that data is an abstract form in itself.  A simple normalisation will remove this issue completely:

From:
id   01jan2018  02Jan2018  03Jan2018

1    abc             def               ghi

To:

id  date            res

1   01jan2018  abc

1   02jan2018  def

1   03jan2018  ghi

...

 

Do note that you can always transpose that data up when you have finished your programming so the end product looks like the starting product, but your programming internally will be much much easier.   

 

Solution
2 weeks ago
Super User
Super User
Posts: 9,599

Re: How to change the value stored in the macro variable change dynamically based on a condition?

Macro is always and only text.  You cannot apply datastep formats to something which does not exist in datastep language. This is one of the reasons i highly recommend you use Base SAS programming for data manipulation, and leave macro requirements out of it.

%let annee=2018;
%let mois=02;

data _null_;  
  call symputx('dobs', put(intnx('day',mdy(&mois,1,&annee),0)-1,date9.));
run;

%put &dobs;
Super User
Posts: 23,683

Re: How to change the value stored in the macro variable change dynamically based on a condition?

It looks like you want the last day of the month. This will depend on the year, what about leap years? Your code doesn't account for that. 

 

It's better to use INTNX() to get the value. You need a starting date for the INTNX function and then an increment but we don't know what you're starting with. 

 

Here's some examples of working with dates. 

 

https://communities.sas.com/t5/SAS-Communities-Library/Macro-Variables-of-Date-and-Time/ta-p/475194

 

If you explain what you want, we can likely help you with a more efficient solution. 

 


@pkopersk wrote:

Hello,

here's what I am trying to accomplish:

 

%let annee=2018;

%let mois=02;

 

 

%macro bs(mois);

%if &mois = 01 %then %let dobs='31jan'&annee;

%if &mois = 02 %then %let dobs='28feb'&annee;

%if &mois = 03 %then %let dobs='31mar'&annee;

%if &mois = 04 %then %let dobs='30apr'&annee;

%if &mois = 05 %then %let dobs='31may'&annee;

%if &mois = 06 %then %let dobs='30jun'&annee;

%if &mois = 07 %then %let dobs='31jul'&annee;

%if &mois = 08 %then %let dobs='31aug'&annee;

%if &mois = 09 %then %let dobs='30sep'&annee;

%if &mois = 10 %then %let dobs='31oct'&annee;

%if &mois = 11 %then %let dobs='30nov'&annee;

%if &mois = 12 %then %let dobs='31dec'&annee;

%mend bs;

%put &dobs;

 

So basically, dobs should have the value "30feb2018"

 


 

Super User
Posts: 6,754

Re: How to change the value stored in the macro variable change dynamically based on a condition?

I don't think you can get 30feb2018, perhaps 28feb2018.  As others have pointed out, sometimes February has 29 days instead of 28.

 

But all that aside, you have two choices.  You can simply remove all the quotes from the macro if you want:

 

28feb2018

 

Or, you can change them to double quotes and move the closing quote if you want:

 

"28feb2018"

 

For that, change the desired expressions:

 

... %then let dobs="28feb&annee";

Super User
Posts: 10,214

Re: How to change the value stored in the macro variable change dynamically based on a condition?

If you use SAS date values (unformatted) in your macro variables, you can use date functions and formats for all this.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 8,075

Re: How to change the value stored in the macro variable change dynamically based on a condition?

[ Edited ]

Use functions.

You don't really need a macro since it is just a one liner.  But if you do make a macro then make it more useful by using parameters and creating a "function" style macro.

%let annee=2018;
%let mois=02;

%macro bs(year,month);
%sysfunc(intnx(month,%sysfunc(mdy(&month,1,&year)),0,e),date9)
%mend bs ;

%let dobs=%bs(&annee,&mois);

Results:

589  %let dobs=%bs(&annee,&mois);
590  %put &=annee &=mois &=dobs ;
ANNEE=2018 MOIS=02 DOBS=28FEB2018

 

☑ This topic is solved.

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

Discussion stats
  • 13 replies
  • 123 views
  • 2 likes
  • 7 in conversation