How could I cut short these 35 repeated statements?

[Mirisage]

Hi SAS Forum,

I have repeated the below "set" statements 35 times, corresponding to Feb 2010 to .......Dec2012.

I have already done the job but this code is highly inefficint.

Could you please help me to macronize these 35 repeated rounds?

data feb_2010_(rename=(balance=FEB2010_balance))
;
set FEB_2010 (keep= bank_number account_number   balance);
run;

data mar_2010_(rename=(  balance=mar2010_balance))
;
set mar_2010 (keep= bank_number account_number   balance);
run;

data apr_2010_(rename=( balance=apr2010_balance))
;
set apr_2010 (keep= bank_number account_number   balance);
run;

data may_2010_(rename=( balance=may2010_balance))
;
set may_2010 (keep= bank_number account_number   balance);
run;

data jun_2010_(rename=( balance=jun2010_balance))
;
set jun_2010 (keep= bank_number account_number   balance);
run;

data JUL_2010_(rename=( balance=JUL2010_balance))
;
set JUL_2010 (keep= bank_number account_number   balance);
run;

data Aug_2010_(rename=( balance=Aug2010_balance))
;
set Aug_2010 (keep= bank_number account_number   balance);
run;

data Sep_2010_(rename=( balance=Sep2010_balance))
;
set Sep_2010 (keep= bank_number account_number   balance);
run;

data Oct_2010_(rename=( balance=Oct2010_balance))
;
set Oct_2010 (keep= bank_number account_number   balance);
run;

data Nov_2010_(rename=( balance=Nov2010_balance))
;
set Nov_2010 (keep= bank_number account_number   balance);
run;

data Dec_2010_(rename=( balance=Dec2010_balance))
;
set Dec_2010 (keep= bank_number account_number   balance);
run;

data Jan_2011_(rename=( balance=Jan2011_balance))
;
set Jan_2011 (keep= bank_number account_number   balance);
run;

data FEB_2011_(rename=( balance=Feb2011_balance))
;
set FEB_2011 (keep= bank_number account_number   balance);
run;

data Mar_2011_(rename=( balance=Mar2011_balance))
;
set Mar_2011 (keep= bank_number account_number   balance);
run;

data Apr_2011_(rename=( balance=Apr2011_balance))
;
set Apr_2011 (keep= bank_number account_number   balance);
run;

data May_2011_(rename=( balance=May2011_balance))
;
set May_2011 (keep= bank_number account_number   balance);
run;

data Jun_2011_(rename=( balance=Jun2011_balance))
;
set Jun_2011 (keep= bank_number account_number   balance);
run;

data Jul_2011_(rename=( balance=Jul2011_balance))
;
set Jul_2011 (keep= bank_number account_number   balance);
run;

data Aug_2011_(rename=( balance=Aug2011_balance))
;
set Aug_2011 (keep= bank_number account_number   balance);
run;

data Sep_2011_(rename=( balance=Sep2011_balance))
;
set Sep_2011 (keep= bank_number account_number   balance);
run;

data Oct_2011_(rename=( balance=Oct2011_balance))
;
set Oct_2011 (keep= bank_number account_number   balance);
run;

data Nov_2011_(rename=( balance=Nov2011_balance))
;
set Nov_2011 (keep= bank_number account_number   balance);
run;

data Dec_2011_(rename=( balance=Dec2011_balance))
;
set Dec_2011 (keep= bank_number account_number   balance);
run;

data Jan_2012_(rename=( balance=Jan2012_balance))
;
set Jan_2012 (keep= bank_number account_number   balance);
run;

data Feb_2012_(rename=( balance=Feb2012_balance))
;
set Feb_2012 (keep= bank_number account_number   balance);
run;

data Mar_2012_(rename=( balance=Mar2012_balance))
;
set Mar_2012 (keep= bank_number account_number   balance);
run;

data Apr_2012_(rename=( balance=Apr2012_balance))
;
set Apr_2012 (keep= bank_number account_number   balance);
run;

data May_2012_(rename=( balance=May2012_balance))
;
set May_2012 (keep= bank_number account_number   balance);
run;

data Jun_2012_(rename=( balance=Jun2012_balance))
;
set Jun_2012 (keep= bank_number account_number   balance);
run;

data Jul_2012_(rename=( balance=Jul2012_balance))
;
set Jul_2012 (keep= bank_number account_number   balance);
run;

data Aug_2012_(rename=( balance=Aug2012_balance))
;
set Aug_2012 (keep= bank_number account_number   balance);
run;

data Sep_2012_(rename=( balance=Sep2012_balance))
;
set Sep_2012 (keep= bank_number account_number   balance);
run;

data Oct_2012_(rename=( balance=Oct2012_balance))
;
set Oct_2012 (keep= bank_number account_number   balance);
run;

data Nov_2012_(rename=( balance=Nov2012_balance))
;
set Nov_2012 (keep= bank_number account_number   balance);
run;

data Dec_2012_(rename=( balance=Dec2012_balance))
;
set Dec_2012 (keep= bank_number account_number   balance);
run;

Thanks

Mirisage

[damanaulakh88 · Accepted solution]

Hi Mirisage,

Please find below the macro for your code:-

======================================

data  FEB_2010;

input bank_number account_number balance;

datalines;

123 1313 232131

322 2321 231232

;

run;

%macro mnth(mon);

data &mon._ (rename=(balance=&mon._balance));

set &mon. (keep= bank_number account_number balance);

run;

proc print data=&mon._;

run;

%mend;

%mnth(FEB_2010);- This you can repeat with any month.

=============================================

Output:-

=======================================================

                                                       bank_    account_    FEB_2010_

                                               Obs    number     number      balance

                                                1       123       1313        232131

                                                2       322       2321        231232

=========================================================

/Daman

[Tom]

Or you could write the macro to take a start and end value.

%macro mnth(start,end);

%local i mon ;

%do i=0 %to %sysfunc(intck(month,"01&start"d,"01&end"d)) ;

  %let mon=%sysfunc(intnx(month,"01&start"d,&i),monyy7.);

  data &mon._ (rename=(balance=&mon._balance));

    set &mon. (keep= bank_number account_number balance);

  run;

%end;

%mend;

[Mirisage]

Hi Daman and Tom,

Many thanks to both of you.

Hi Tom,

I like to learn your automation code.

So, I ran your code in SAS editor.

%macro mnth(start,end);

%local i mon ;

%do i=0 %to %sysfunc(intck(month,"01&start"d,"01&end"d)) ;

  %let mon=%sysfunc(intnx(month,"01&start"d,&i),monyy7.);

  data &mon._ (rename=(balance=&mon._balance));

    set &mon. (keep= bank_number account_number balance);

  run;

%end;

%mend;

%mnth (start=Feb2010, end=Dec2012);

It gives a complain like this.

4         %mnth (start=Feb2010, end=Dec2012);

SYMBOLGEN:  Macro variable START resolves to Feb2010

SYMBOLGEN:  Macro variable END resolves to Dec2012

SYMBOLGEN:  Macro variable START resolves to Feb2010

SYMBOLGEN:  Macro variable I resolves to 0

SYMBOLGEN:  Macro variable MON resolves to FEB2010

SYMBOLGEN:  Macro variable MON resolves to FEB2010

SYMBOLGEN:  Macro variable MON resolves to FEB2010

ERROR: File WORK.FEB2010.DATA does not exist.

Could you please let me know how to fix this problem.

Thanks Mirisage

[Tom]

The macro is generating the macro variable MON in format of MMMYYYY.  Your original naming convention in the code you posted had extra underscores in the dataset names.  Personally I would remove the extra underscores from the dataset names, but you might not have that option. Also I am not sure why you are creating so many individual datasets anyway.  Why not just make one dataset with a MONTH or DATE column to distinguish the groupings?

You can fix it in a number of ways. Here is one. Parse the month and the year part of the current date into separated macro variables and then you can stick in the extra underscores where you need them.

%macro mnth(start,end);

%local i mon year;

%do i=0 %to %sysfunc(intck(month,"01&start"d,"01&end"d)) ;

  %let mon=%sysfunc(intnx(month,"01&start"d,&i),monyy7.);

  %let year=%substr(&mon,4);

  %let mon =%substr(&mon,1,3);

  data &mon._&year._ (rename=(balance=&mon.&year._balance));

    set &mon._&year (keep= bank_number account_number balance);

  run;

%end;

%mend;

[Mirisage]

Hi Tom,

Thank you very much for these insights and your clues. These are extremely helpful.

Thanks again!

Mirisage