topic Re: Macros in SAS Programming

Macros

asuman — Sat, 25 Sep 2021 12:37:47 GMT

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.

Re: Macros

Kurt_Bremser — Sat, 25 Sep 2021 13:02:18 GMT

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.

Re: Macros

asuman — Sat, 25 Sep 2021 13:10:05 GMT

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.

Re: Macros

Shmuel — Sat, 25 Sep 2021 13:45:46 GMT

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;

Re: Macros

PaigeMiller — Sun, 26 Sep 2021 10:48:25 GMT

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;

Re: Macros

Shmuel — Sat, 25 Sep 2021 19:58:00 GMT

@PaigeMiller, you nicely expanded my solution.

Re: Macros

asuman — Sun, 26 Sep 2021 14:33:07 GMT

Thanks Paige and Shmuel.