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

[pkopersk]

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"

[novinosrin]

%let annee=2018;

%let mois=02;

%put &mois;
　

%macro bs;

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

%put &dobs;
%mend bs;
%bs

[RW9]

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.

[pkopersk]

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

[Kurt_Bremser]

---

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

[pkopersk]

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.

[Kurt_Bremser]

---

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

[pkopersk]

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

[RW9]

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.   

[RW9 · Accepted solution]

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;

[Reeza]

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"

---

[Astounding]

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";

[Kurt_Bremser]

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

[Tom]

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