macro not populating variables expected

[jenim514]

Hi all!  I need a little help trouble shooting this program.  basically, I need the ORD and SORD variables to populate with a number depending on the table name.    I am currently getting all null values because the last table has zero obs (its a freq table).  So I think I'm missing a loop or something so it's not overwriting?

For example, if table=A then ORD=1 and SORD=1;

If table=P then ORD=4 and SORD=14;

%macro freq (table,ord, sord);
data freqs ;
set allae (in=a) allsae (in=b) alldlt  (in=c) anyae (in=d) anyteg3 (in=e) anyrte (in=f) anyg3rte (in=g) anysae (in=h) anyrsae (in=i) anyrsg3s (in=j)
anydlt (in=k) anydisc (in=l) anyinter (in=m) anyreduc (in=n) anyfaed (in=o) anyfrae (in=p);
by tx;
if &table then do;
	ord=⩝
	sord=&sord;
	end; 
run;
%mend freq;

%freq (a,1,1);
%freq (b,2,1);
%freq (c,3,1);
%freq (d,4,2);
%freq (e,4,3);
%freq (f,4,4);
%freq (g,4,5);
%freq (h,4,6);
%freq (i,4,7);
%freq (j,4,8);
%freq (k,4,9);
%freq (l,4,10);
%freq (m,4,11);
%freq (n,4,12);
%freq (o,4,13);
%freq (p,4,14);

Help is appreciated!!

[ballardw]

Do any of those data sets already have variables named any of a through p?

You do realize that each run of the macro replaces the previous data set don't you? And the last data set probably has null values for most of the records since your "p" data set comes after a whole bunch.

Did you test this an get a proper result with only two input sets before adding the other dozen or so?

What is the purpose of this macro? The name Freqs is pretty misleading as nothing is counted and no "frequencies" are generated?

It usually helps to provide a little bit of example input data (maybe 5 records from each of the first 2 or 3 data sets) and what the desired result for those records would be. Only include as many variables as needed to make things clear.

[jenim514]

Yes, I just realized the last dataset is overwritting. These are all frequency tables I need to build a RTF table. So I need to set the orders. I can do this the log way with 'output' but is there a way to do the repetitive step for all these stacked tables without it overwriting?

long way...

if a then do;
ord=1;
sord=1
end;
if b then do;
ord=2;
sord=1
end;
if c then do;
ord=3;
sord=1
end;

etc.....

[Reeza]

You probably need some sort of macro loop OR not a macro at all. 

I'm inclined to go down the not a macro route at all. In that case, I'd probably generate some variables I could join on or lookup or possibly a format instead. At the end of the day, it's a lookup problem. 

[ballardw]

---

@jenim514 wrote:

Yes, I just realized the last dataset is overwritting. These are all frequency tables I need to build a RTF table. So I need to set the orders. I can do this the log way with 'output' but is there a way to do the repetitive step for all these stacked tables without it overwriting?

long way...

if a then do;
ord=1;
sord=1
end;
if b then do;
ord=2;
sord=1
end;
if c then do;
ord=3;
sord=1
end;

etc.....

---

Assuming all the frequency tables originated from the same data perhaps you would be better served with a different procedure such as Proc Tabulate which will create a single table with frequencies of different variables:

proc tabulate data=sashelp.class;
   class sex;
   class age;
   tables age sex,
          n='Count'
   ;
run;
          

You can have the same variables involved in more that one place as well:

proc tabulate data=sashelp.class;
   class sex;
   class age;
   tables age sex age*sex,
          n='Count'
          
   ;
run;

[Astounding · Accepted solution]

A "least amount of change" approach would be to change the definition of the macro:

%macro freqs (table, ord, sord);

   if &table then do;

      ord=⩝

      sord=&sord;

   end;

%mend freqs;

Then begin with:

data freqs ;
set allae (in=a) allsae (in=b) alldlt  (in=c) anyae (in=d) anyteg3 (in=e) anyrte (in=f) anyg3rte (in=g) anysae (in=h) anyrsae (in=i) anyrsg3s (in=j)
anydlt (in=k) anydisc (in=l) anyinter (in=m) anyreduc (in=n) anyfaed (in=o) anyfrae (in=p);
by tx;

Then call all the macros just as you are doing now.

Then add:

run;

[Tom]

You put the DATA statement and RUN statement in the wrong place. Put them around the macro calls.

%macro freq (table,ord, sord);
if &table then do;
  ord=⩝
  sord=&sord;
end; 
%mend freq;

data freqs ;
  set allae (in=a) allsae (in=b) alldlt  (in=c) anyae (in=d) 
      anyteg3 (in=e) anyrte (in=f) anyg3rte (in=g) anysae (in=h) 
      anyrsae (in=i) anyrsg3s (in=j) anydlt (in=k) anydisc (in=l) 
      anyinter (in=m) anyreduc (in=n) anyfaed (in=o) anyfrae (in=p)
  ;
  by tx;
%freq (a,1,1);
%freq (b,2,1);
%freq (c,3,1);
...
run;