Date macro variable char to number

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

Date macro variable char to number

[ Edited ]

Hello All,

 

I need urgent help for one of my task where I am automating the dates variable. &Year1 macro variable I have already defined it in data null statement earlier which is taking current year which is 2017

 

The issue is here in invoking with month

 

Proc SQL;

Select

Var1,

Var2,

Var3,

Var4

%do i=2 %to &monthmax +1;

%let date=%sysfunc(intnx(month, %sysfunc(today()), -&i.), monname3.);

(SUM(&date._&year1._Actuals))FORMAT=BEST12. AS &date&year1 ,

%end;

Variable 6

from table ;

quit;

 

The issue is for the 5th variable which is &date&year1. It will create in the loop like this

(Sum(Jan_2017_Actuals)) as JAN2017,

(Sum(FEB_2017_Actuals)) as FEB2017,

(Sum(MAR_2017_Actuals)) as MAR2017,

(Sum(APR_2017_Actuals)) as APR2017,

(Sum(MAY_2017_Actuals)) as MAY2017,

 

So the variables names are jan_2017_actual, feb_2017_actuals etc and it is getting renamed as JAN2017, FEB2017 but my requirement is different. I need to rename it as "31JAN2017"n, "28FEB2017"n, "31MAR2017"n using the same macro variable &date. Is it possible in the same PROC SQL Step? It would be fine if I can later rename it in a seperate data step also as "31JAN2017"n instead of JAN2017 but there has to be loop where it should be upto the reporting month which is one month prior to the current month like above macro I used. Your help would be appreciated. I need exact code.

 

 


Accepted Solutions
Solution
‎07-15-2017 06:08 AM
Super User
Posts: 5,513

Re: Date macro variable char to number

[ Edited ]
Posted in reply to sameer112217

You're already most of the way there.  You have a %LET statement to create &DATE.  Similarly, create a string that holds the last day of the month:

 

%let last_day = %sysfunc(intnx(month, %sysfunc(today()), - &i., e), date9.);

 

Then use that in the SELECT clause by replacing this:

 

as &date&year1 ,

 

Instead, use:

 

as "&last_day"n ,

 

That's all assuming that you have set the VALIDVARNAMES option to ANY, so that name literals are legal.

View solution in original post


All Replies
Super User
Super User
Posts: 7,977

Re: Date macro variable char to number

Posted in reply to sameer112217

Normalise the data so you have all your data going down, then group by that, then if needs be transpose up again using the date as label NOT as variable name:

proc transpose data=have out=temp;
  by var1--var4;
  var _numeric_;
run;

proc sql;
  create table temp2 as
  select  var1,var2,var3,var4,sum(...) as result
  from   temp1
  group by var1,var2,var3,var4;
quit;

proc transpose data=temp2 out=want;
  by var1--var4;
  var ...;
run;

As I don't have data i can't run to test, but this should work as a basis.  It is never a good idea to call your variable names "date"n.  Names are for programming purposes.  And it is far easier to normalise, do processing, transpose up than trying to force it through macro. 

Contributor
Posts: 56

Re: Date macro variable char to number

Hello RW9

 

Thanks for the input. Transpose in my case wont work. I understand naming variable as "date"n is a bad idea but the client requirement is that. Transpose wont solve my concern. Is there any way I can use the &date macro variable and generate "date9"n values in the same loop in the rename variable in PROC SQL...

 

It is not necessary that it should be in the same PROC SQL but it can be in date step but upto reporting month which is one month prior to current month...

Solution
‎07-15-2017 06:08 AM
Super User
Posts: 5,513

Re: Date macro variable char to number

[ Edited ]
Posted in reply to sameer112217

You're already most of the way there.  You have a %LET statement to create &DATE.  Similarly, create a string that holds the last day of the month:

 

%let last_day = %sysfunc(intnx(month, %sysfunc(today()), - &i., e), date9.);

 

Then use that in the SELECT clause by replacing this:

 

as &date&year1 ,

 

Instead, use:

 

as "&last_day"n ,

 

That's all assuming that you have set the VALIDVARNAMES option to ANY, so that name literals are legal.

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 117 views
  • 2 likes
  • 3 in conversation