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

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"

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

13 REPLIES 13
novinosrin
Tourmaline | Level 20

%let annee=2018;

%let mois=02;


%put &mois;
 

%macro bs;

 

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


%put &dobs;
%mend bs;
%bs

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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
Super User

@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
Fluorite | Level 6
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
Super User

@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
Fluorite | Level 6
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
PROC Star

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

Tom
Super User Tom
Super User

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

 

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
  • 13 replies
  • 10315 views
  • 2 likes
  • 7 in conversation