Using macros and proc freq to make datasets but need to display 0 where needed.

[ematznick]

I'm am writing/running a macro that takes a data set and finds the frequencies of a variable and also the variable by groups. Then every table made (3) are put into one table but when the tables are combined any group with a 0 count just shifts the other counts to the left. I'm trying to figure out how to put a placeholder into the table so the table doesn't shift over. 

%macro disc_tab (dset, var,grp1,grp2,ct,ct1,ct2,ct3,ct4,ctc,name,ord);
proc sort data= &dset out=sort1&dset;
	by &grp1;
proc freq data= sort1&dset;
	table &var/out =outtot&var sparse;
	where &var ne '';

proc freq data=sort1&dset;
	table &var/out=out1&var sparse;
	where &var ne '';
	by &grp1;
proc sort data= &dset out=sort2&dset;
	by &grp2;
proc freq data=sort2&dset;
	table &var/out=out2&var sparse;
	where &var ne '';
	by &grp2;
data outtot&var;
set outtot&var;
	npc=trim(left(count))||"/&ct ("||trim(left(put(percent,5.1)))||')';
data out1&var;
set out1&var;
if subcoh =1 then npc=trim(left(count))||"/&ct1 ("||trim(left(put(percent,5.1)))||')';
if subcoh =2 then npc=trim(left(count))||"/&ct2 ("||trim(left(put(percent,5.1)))||')';
if subcoh =3 then npc=trim(left(count))||"/&ct3 ("||trim(left(put(percent,5.1)))||')';
if subcoh =4 then npc=trim(left(count))||"/&ct4 ("||trim(left(put(percent,5.1)))||')';
data out2&var;
set out2&var;
if comb =0 then npc=trim(left(count))||"/&ct1 ("||trim(left(put(percent,5.1)))||')';
if comb =1 then npc=trim(left(count))||"/&ctc ("||trim(left(put(percent,5.1)))||')';
data out&var;
	set outtot&var out1&var out2&var;
proc SORT; 
	BY  &var;
PROC TRANSPOSE data=out&var out=trans&var;
   VAR npc; 
	BY  &var;
run;

Data table&var;
	length stat $300.;
	FORMAT stat var $300.;
	set trans&var;
	stat=&var;
	drop COL6 ;
	var=&name;
	ord=⩝

run;
%mend disc_tab;

%disc_tab(mh_analysis,mhpreabsp_display,subcoh,comb,&n_pre,&n1_pre,&n2_pre,&n3_pre,&n4_pre,&nc_pre,'name' [N (%)]',9);

[Tom]

In your macro call your last parameter has unbalanced quotes.

What are the values of those input macro variables referenced in the macro call? Where did the values come from?

Once you know what SAS code you want the macro to generate getting it to generate that code should be easy.

What code do you want to run for this set of input parameters?  

%disc_tab
(dset=mh_analysis
,var=mhpreabsp_display
,grp1=subcoh
,grp2=comb
,ct=&n_pre
,ct1=&n1_pre
,ct2=&n2_pre
,ct3=&n3_pre
,ct4=&n4_pre
,ctc=&nc_pre
,name='name [N (%)]'
,ord=9
);

[ematznick]

The unbalanced quotes is just a copy and edit error my bad.
All the ct variables are just total amounts I want displayed so it gives something like 6/25 (2.4%).
dset is my full dataset.
var is the variables which are strings for each group. example: 'elbow', 'knee', etc but each variable has different options for strings. grp1 and grp2 are different cohorts coded either 1-4 or 0-1.
name is just the name of what is being done.
and ord is the order I want once I combine all the run macro statements.

I'm trying to avoid coding each 0 individually since I'm running 10 different variables into the macro some with 2 groups, some with 3, some with 7.

[Tom]

I don't understand this comment:

I'm trying to avoid coding each 0 individually since I'm running 10 different variables into the macro some with 2 groups, some with 3, some with 7

What are the zeros?  Are you talking about values for categorical variable that do not appear in the data?  How is SAS supposed to know what values are possible?  Do you have a format attached to the variable?  Some additional dataset with the list of possible values?

Please explain what your goal is.  Your inputs, Desired output, logic or rules for getting form input to output.

Show the SAS code you are trying to run (without macro) and to produce that output and explain which parts of the code need to vary in a way that using a macro to generate the code is required.

[Reeza]

It looks like you're already using the SPARSE option in your PROC FREQ so that will cover cases where you have at least one value for one category. But if the data doesn't exist in your data you have to tell SAS somehow. The logistically simplest approach is to build a skeleton table that has all the values you want and then you merge in your reporting data with your skeleton table. This requires you to consider your design from scratch as you do need some way to identify all unique values required first. 

These examples may help you by the way - they use a different approach that may make your life a bit simpler. 

Two way table (Age*vars)

https://gist.github.com/statgeek/0c4aeec9053cf8050be18a03b842c1b9

One variable at a time method:

https://gist.github.com/statgeek/e0903d269d4a71316a4e

[ematznick]

Thanks so much!