Macros

[asuman]

data t1;
input year volume diff_pr prob forb;
cards;
2018 204010 2380 4898 5740
2019 208765 4678 7659 9862
2020 279868 7654 8762 8257
2021 215678 8721 9821 7627
;
run;
data t2;
set t1;
match_rate_1 = (volume-diff_pr)/volume;
match_rate_2 = (volume-prob)/volume;
match_rate_3 = (volume-forb)/volume;
run;

How can we automate the way match_rate is calculated.

[Kurt_Bremser]

I see nothing to automate here; the code will calculate the whole dataset on its own.

Edit: and if you want to make this dynamic, you need to tell us which parts have to change, and the rules for the change.

[asuman]

Hi Kurt,
My question is that if in table t1 we have 20 more columns then in table t2 we have to define match rate for those 20 columns also.And if we use different tables, then every time we have to make changes in match rate formula as per new columns.
so i am asking is there any way we can automate such a way like match rate will be calculated based on the columns in table t1.

[PaigeMiller · Accepted solution]

Certainly, @Shmuel has a better solution than macros.

But, @asuman, I really wonder about the value of associating variable diff_pr to match_rate_1, prob to match_rate_2 and forb to match_rate_3, how does assigning a sequential number to an otherwise constant variable name help anything for anyone? You are creating unintelligible and meaningless names. It seems as if you are causing confusion and difficulty, rather than bringing some sort of understanding to the new variable names.

Instead of match_rate_1 through match_rate_3, how about diff_pr_rate, prob_rate and forb_rate?????? Now, users can EASILY understand which rate is which variable. That's a solution I (and others) can sink my teeth into, figuratively speaking of course. And still, you don't need a macro, but you do need a macro variable (or at least, that's how I would do it, maybe there is a way to do this without using a macro variable).

proc sql;   
    select name into :orignames separated by ' ' from dictionary.columns where libname='WORK' and memname='T1' and 
        name not in ('year','volume');
    select cats(name,'_rate') into :ratenames separated by ' '
        from dictionary.columns where libname='WORK' and memname='T1' and 
        name not in ('year','volume');
quit;

data t2;
  set t1;
       array vx {*} &orignames;
       array mr &ratenames;
       do i=1 to dim(vx);
           mr(i) = (volume - vx(i)) / volume; 
       end;
run;

[Shmuel]

@PaigeMiller, you nicely expanded my solution.

[asuman]

Thanks Paige and Shmuel.

[Shmuel]

If I understand you correctly, what you are looking for is not a macro but using array,

as demostrated in next code:

data t2;
  set t1;
       array mr {*} match_rate1-match_rate_3;
       array vx {*} diff_pr prob forb;
       do i=1 to dim(vx);
           mr(i) = (volume - vx(i)) / volume;
       end;
run;