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

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

pkopersk — Tue, 03 Jul 2018 15:43:55 GMT

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"

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

novinosrin — Tue, 03 Jul 2018 15:50:59 GMT

%let annee=2018;

%let mois=02;

%put &mois;
　

%macro bs;

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

%put &dobs;
%mend bs;
%bs

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

RW9 — Tue, 03 Jul 2018 15:54:09 GMT

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.

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

Reeza — Tue, 03 Jul 2018 16:30:55 GMT

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"

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

Astounding — Tue, 03 Jul 2018 16:38:31 GMT

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

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

Kurt_Bremser — Tue, 03 Jul 2018 16:56:06 GMT

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

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

pkopersk — Fri, 06 Jul 2018 09:57:01 GMT

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

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

Kurt_Bremser — Fri, 06 Jul 2018 10:23:53 GMT

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

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

RW9 — Fri, 06 Jul 2018 10:24:20 GMT

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;

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

pkopersk — Fri, 06 Jul 2018 10:30:16 GMT

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.

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

Kurt_Bremser — Fri, 06 Jul 2018 11:15:59 GMT

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

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

RW9 — Fri, 06 Jul 2018 10:56:43 GMT

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.

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

pkopersk — Fri, 06 Jul 2018 11:14:15 GMT

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

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

Tom — Fri, 06 Jul 2018 14:20:34 GMT

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