topic Macro Do Loops in SAS Procedures

Macro Do Loops

DrBigAl — Wed, 22 Aug 2018 13:29:48 GMT

Hi Guys!

I'm in a little pickle here. Let me explain....

The overall project is to take information from the variable columns and give then a weight then score. However, there are so many variables to weigh and score that it would be really tedious to do each one individually. I am looking for a macro paired with do loops to process this data in short, efficient code. For example:

proc sort data=have
out=want;
by id year descending doc_num;
run;

data _null_;
set have(where=(year=&curryr. or year=&prevyr. and doc_num=&.));

by id;
if name then a_weight = 'L' and a_score='L';
if first.sale_dt = . and last.sale_dt = . then weight='M' and score='M';
  else if first.sale_dt is not . and last.sale_dt = . then weight='M' and score='L';
  else if first.sale_dt = . and last.sale_dt is not . then weight = 'M' and score='L';
  else if first.sale_dt ^= last.sale_dt then weight='M' and score='M';
  else if first.sale_dt = last.sale_dt then weight='M' and score='L';

run;

The other variables and weights will be different than above, but the expressions will stay the same.....

For instance: if price is missing on both days then weight ='L' and score='L' and so on.....

if product is missing on both days then weight='M' and score='L' and so on....

The final objective is to put the weight and score through a calculation (adding up all of the scores with a corresponding number) to get the total document score.

Suggestions?

Re: Macro Do Loops

Astounding — Tue, 21 Aug 2018 19:03:00 GMT

A macro can generate all the code you might need, but you can't do that yet. First, you must have working code for one individual case. Then you can contemplate using a macro to expand that to many cases ... if a macro is needed. But the program that you posted is not working code. So get it working first, then we can talk about the next step.

Re: Macro Do Loops

ballardw — Tue, 21 Aug 2018 20:27:18 GMT

You do realize that this snippet of code

 set have(where=(year=&curryr. and year=&prevyr.

is only going to have any output records when &curryr = &prevyr don't you? In which case why are there two comparisons?

Or do you want records when the value of the year variable is either &curryr. or &prevyr?

Re: Macro Do Loops

DrBigAl — Tue, 21 Aug 2018 23:19:44 GMT

I am comparing records from this year and last year. I would only want the records for 2017 and 2018. So or is correct.

I couldn't test the code because I only have table names. Data hasn't been loaded as of yet. I really just need to map things out with a plan until the data is loaded

Re: Macro Do Loops

ballardw — Wed, 22 Aug 2018 15:19:34 GMT

@DrBigAl wrote:
I am comparing records from this year and last year. I would only want the records for 2017 and 2018. So or is correct.

I couldn't test the code because I only have table names. Data hasn't been loaded as of yet. I really just need to map things out with a plan until the data is loaded

Instead of the "AND" which will not select any records look at

year in (&curryr. &prevyr.)

which is an OR condition.

Please examine the results of this code and see which one you want. This will be have exactly the same as your "where" logic for the year variable.

data example;
   curryr = 2017;
   prevyr = 2018;
   year = 2017;
   if year=2017 and year=2018 then value= 'Yes Match';
   else value= 'No Match';
   if year in (2017 2018) then oth = 'Yes Match';
   else oth = 'No Match';

run;

Re: Macro Do Loops

DrBigAl — Wed, 22 Aug 2018 19:15:24 GMT

Thank you! This helped me refocus my efforts.