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

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

sameer112217
Quartz | Level 8

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

Astounding
PROC Star

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.

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
  • 3 replies
  • 415 views
  • 2 likes
  • 3 in conversation