topic Re: Change variable/column names to monthly increments in SAS Programming

Change variable/column names to monthly increments

orange444 — Tue, 27 Aug 2019 14:40:20 GMT

Hi - first time poster here so will try my best to describe the problem I am facing. Also fairly newish to SAS.

I have a number of variables all in one data set called:

Coefficient_t0 Coefficient_t1 Coefficient_t2 Coefficient_t3 etc up to a large number of coefficients (~100).

I would like to somehow rename these to

Jun18 July18 August18 Spetember 18 etc...

I was hoping to loop over the values of t0, t1, t2 and assign each = intnx('month',Start_Date,i,"BEGINNING") this way each value of i should be a new month.

Something like:

%let nvars=20;

%macro doit;

data Maturity_Coeffient_all_t_date;

set Maturity_Coeffient_all_t;

%do i = 1 %to &nvars.;

rename Coefficient_t&i. = intnx('month',Start_Date,i,"BEGINNING");
%end;

run;

%mend;

%doit

However this won't work...

Any way to fix this?

Re: Change variable/column names to monthly increments

PaigeMiller — Tue, 27 Aug 2019 14:54:51 GMT

@orange444 wrote:

Hi - first time poster here so will try my best to describe the problem I am facing. Also fairly newish to SAS.

I have a number of variables all in one data set called:

Coefficient_t0 Coefficient_t1 Coefficient_t2 Coefficient_t3 etc up to a large number of coefficients (~100).

I would like to somehow rename these to

Jun18 July18 August18 Spetember 18 etc...

I was hoping to loop over the values of t0, t1, t2 and assign each = intnx('month',Start_Date,i,"BEGINNING") this way each value of i should be a new month.

Something like:

%let nvars=20;

%macro doit;

data Maturity_Coeffient_all_t_date;

set Maturity_Coeffient_all_t;

%do i = 1 %to &nvars.;

rename Coefficient_t&i. = intnx('month',Start_Date,i,"BEGINNING");
%end;

run;

%mend;

%doit

However this won't work...

Any way to fix this?

Honestly, having variable names of your columns to be Jun18 July18 etc. isn't really a good way to arrange data sets in SAS, although there are exceptions, such as if the data set has to be exported to Excel.

As with all macro writing, you want to first (in other words, before you write a single macro variable into your code) get working valid legal SAS code that does what you want. In this case you would see that

rename Coefficient_t01 = intnx('month',Start_Date,i,"BEGINNING");

(note there are no macro variables in the above code) does not work. You would need to fix this in order to have any chance of getting a macro to work.

Having said all of the above, your %DO loop should look like something like this:


%let start_date=%sysevalf('01JUL18'd); 
%do i=1 %to &nvars;
    %let y=%sysfunc(intnx(month,&Start_Date,&i));
    %let yf=%sysfunc(putn(&y,monyy5.));
    %put &=start_date &=y &=yf; /* This line is optional */
    rename coefficient_t&i = &yf;
%end;

Re: Change variable/column names to monthly increments

Tom — Tue, 27 Aug 2019 14:58:20 GMT

One way to see what is happening is to turn on MPRINT option so you can see what SAS code your macro is generating. Then you can adopt the macro to generate the SAS code you need.

Right now you macro is generating code like this:

MPRINT(DOIT):   rename Coefficient_t1 = intnx('month',Start_Date,i,"BEGINNING");
MPRINT(DOIT):   rename Coefficient_t2 = intnx('month',Start_Date,i,"BEGINNING");

Which is clearly not valid SAS syntax. The RENAME statement wants a NAME after the equal sign and not function call.

You can call SAS functions in macro code by using the %SYSFUNC() macro function. Put then functions arguments also need to make sense. INTNX() function wants a date for the second argument, not a string of letters. Similarly for the third argument.

Also what value do you want to use for the name? Currently you appear to be generating a date value. So the number of days since 1960. You probably want to apply a format to that date value so it generates something that is human readable.

Also there are only 12 months in a year. So you cannot make 20 variables with names like the 18th of September, 18th of October etc. Are you trying to imply you want the names to reflect just the month and year? If so then use four digits for the year or you will confuse people.

%let nvars=20;
%let start_date='01SEP2018'd;

%macro doit();
data Maturity_Coeffient_all_t_date;
  set Maturity_Coeffient_all_t;
  rename
%do i = 1 %to &nvars.;
   Coefficient_t&i. = %sysfunc(intnx(month,&Start_Date,&i-1,B),monyy7)
%end;
  ;
run;
%mend ;
options mprint ;
%doit()

Result:

381   options mprint ;
382   %doit()
MPRINT(DOIT):   data Maturity_Coeffient_all_t_date;
MPRINT(DOIT):   set Maturity_Coeffient_all_t;
MPRINT(DOIT):   rename Coefficient_t1 = SEP2018 Coefficient_t2 = OCT2018 Coefficient_t3 = NOV2018
Coefficient_t4 = DEC2018 Coefficient_t5 = JAN2019 Coefficient_t6 = FEB2019 Coefficient_t7 = MAR2019
Coefficient_t8 = APR2019 Coefficient_t9 = MAY2019 Coefficient_t10 = JUN2019 Coefficient_t11 = JUL2019
Coefficient_t12 = AUG2019 Coefficient_t13 = SEP2019 Coefficient_t14 = OCT2019 Coefficient_t15 = NOV2019
Coefficient_t16 = DEC2019 Coefficient_t17 = JAN2020 Coefficient_t18 = FEB2020 Coefficient_t19 = MAR2020
Coefficient_t20 = APR2020 ;
MPRINT(DOIT):   run;

Re: Change variable/column names to monthly increments

PeterClemmensen — Tue, 27 Aug 2019 15:01:48 GMT

Another approach using PROC DATASETS

data have;
    array Coefficient_t{100} (100*0);
run;

data _null_;
    array Coefficient_t{100};
    length rname $5000;
    date='01May18'd;
    do i=1 to 100;
        date=intnx('month', date, 1, 'b');
        rname=catx(' ',rname, cats(vname(Coefficient_t[i]), '=', put(date, monyy7.)));
    end;
    retain rname;
    call symputx('rname', rname);
run;

%put &rname.;

proc datasets lib=work nolist;
    modify have;
        rename &rname.;
run;quit;

proc print data=have;run;