Require help with a Macro and to remove hard coding ?

[Allaluiah]

HI SAS Professionals,

My SAS code process has been written to import data from the database tables and I guess to create a few reports. I am in need of a macro to be able to reuse the same in production year after year. I am in need of help to parameterise in certain places in the codes with a macro and remove hard coding as the values are subject to change next year.

Right now,

1. I have the dataset names like this: libref.year2013_datasetname and likewise it so occurs that all the datasets my process creates will also have dataset names such as libref.year2013_datasetname1............year2013_datasetnameN, which would be deemed wrong and wont work when data changes in the database next year as it would be year2014 next year.

2. I also noticed there are some hard coding in the script like:

if A= american then nominee= xxxx;

else if A=mexican then nominee=yyyy;

else if A=latin american the nominee=zzzz;

else if A=african then nominee=gggg;

So again the issue here is that the values are subject to change year after year in the database, therefore the above hard code needs to be changed in order to make it useful over and over again without touching it.So, when i look through it, i find there are many such instances.

I'm afraid I'm not a SAS guy and i'm basically covering for someone who has gone on leave and the situation got so desperate. Sorry for the bother.

[ballardw]

It looks like you have done at least part of the first two steps: 1) have working code and 2) Identify the parts that need to change.

For this you probably want to use named parameters to make the code easier to read. For things like your data sets this is common: (this is stub code just to show likely use)

%macro dummy (lib= , processyear= );

Data newdata;

     set &lib..year&processyear._datasetname;  /* this follows your example, note the . to connect the pieces of library and dataset name, any way &processyear has the value you plug into the name as needed*/

run;

%mend;

If the library doesnt change often you can assign a default in the macro definition but if it NEVER changes you could leave it hardcoded and not pass as parameter

%macro dummy(lib=mylib, processyear=);

The Nominee variables can be handled in a similar fashion

%macro dummy(lib=mylib, processyear=, AmNom=, MexNom=,LatAmNom=, AfNom=);

... This assues Nominee is a character variable and you are passing names, if numeric then the quotes aren't needed. The quotes below do need to be the double quote to resolve properly

if A=american then nominee= "&AmNom";

else if A=mexican then nominee= "&MexNom";

else if A=LatinAmerican then nominee= "&LatAmNom";

/*continue the pattern*/

%mend;

[Allaluiah]

Sir,

Thank you for the very nice guidance particularly for your valuable time. I did comprehend a fair bit, however i just thought of mentioning how the hard code looks like so that the practical solution can make understanding easier for a SAS beginner.

Proc sort data=libref.year2013_dataset1 out=libref.dataset1_sorted

By variable1;

Run;

Proc sort data=libref.year2013_dataset2 out=libref.dataset2_sorted;

By variable1;

Run;

Data libref.dataset3;

Merge libref.dataset1_sorted (in=a) libref.dataset2_sorted(in=b);

By variable1;

If a;

Run;

Proc sort data=libref.dataset3 out=libref.dataset3_sorted;

By variable2;

Run;

Data libref.dataset4 ;

Set libref.dataset3_sorted;

var6= ‘valu.xx’;

var7=var6;

var8= var6' || var9;

SELECT (country);/*variable named country in incoming dataset*/

WHEN (‘usa’)  ID = ‘usa.american’;

WHEN (‘australian’) ID= ‘aus.ozzy’;

When (‘england’) ID=’uk.eng’;

When (‘canadian’) ID=’ca. maple’;

When (‘mexico’) SK_ID=’mx.hispanic’;

continues till all wriiten;

Otherwise;

End;run;

[ballardw]

Generally the bit I posted before applies directly. The difference is replacing the hardcoded bits with the macro parameters.

This code:

Proc sort data=libref.year2013_dataset1 out=libref.dataset1_sorted

By variable1;

Run;

Proc sort data=libref.year2013_dataset2 out=libref.dataset2_sorted;

By variable1;

Run;

Data libref.dataset3;

Merge libref.dataset1_sorted (in=a) libref.dataset2_sorted(in=b);

By variable1;

If a;

Run;

Proc sort data=libref.dataset3 out=libref.dataset3_sorted;

By variable2;

Run;

Could be replaced by:

Proc sql;

     Create table libref.dataset3_sorted as

     select a.*,b.*

     from libref.year2013_dataset1 as a left join libref.year2013_dataset2 as b on

          a.variable1=b.variable1

     order by b.variable2;

quit;

For a simple macro test you can create macro variables without a macro using let.

%let procesyear=2013;

Proc sql;

     Create table libref.dataset3_sorted as

     select a.*,b.*

     from libref.year&processyear._dataset1 as a left join libref.year&processyear._dataset2 as b on

          a.variable1=b.variable1

     order by b.variable2;

quit;