output sas statements into macro variable to resolve to desired output

[yashraj89]

I have a macro in which there are keyword parameters like this.

colvars=C1 C3 C6 ,
collabl1=SCLC|(N=&m_C1)|n(%),
collabl2=NSCLC|Non-Squamous|(N=&m_C3)|n(%),
collabl3=GEJ|(N=&m_C6)|n(%),

This will assign my title column lables in my output report.

I want to automate this by getting the number of variables in the data. 

If there are 4 vars in the data it should automate by knowing there are 4 variables in the dataset and get that information into the macro.

colvars=C1 C2 C3 C5 ,
collabl1=SCLC|(N=&m_C1)|n(%),
collabl2=NSCLC|Squamous|(N=&m_C2)|n(%),

collabl3=ESCC|(N=&m_C3)|n(%),
collabl4=GEJ|(N=&m_C6)|n(%),

I want to automate this and get these steps into the macro call, based on the number of variables in the dataset. 

%rtf(inds=final,
rowheadvars=rowhead1 rowhead2,
rowheadlabel=,
colvars=C1 C3 C6 Czz,
collabl1=SCLC|(N=&m_C1)|n(%),
collabl2=NSCLC|Non-Squamous|(N=&m_C3)|n(%),
collabl3=GEJ|(N=&m_C6)|n(%),

collabl4=Total|(N=&m_Czz)|n(%),
colwidths=39 15 15 15 15,
lindent=700,
sortordvars=varord ordvar1,
linespp=18,
orientation=landscape
);

I have created the list of vars and the count of vars but couldnt able to figure out how to iterate them into the macro. 

final is the dataset from which i am taking the nonmissing vars.
proc sql noprint;
select distinct name
into :vars separated by ' '
from dictionary.columns
where memname = 'FINAL' and prxmatch("m/C1|C2|C3|C4|C5|C6|CZZ/oi",strip(name))> 0
order by varnum;
quit;
%let cntlist = &sqlobs;

%put &vars. ;
%put &cntlist. ;

options mlogic mprint symbolgen;
%macro wordcount(list);

%local count;
%let count=0;
%do %while(%qscan(&list,&count+1,%str( )) ne %str());
%let count = %eval(&count+1);
%end;

%mend wordcount;

proc format ;

value $chrt
"C1" = "SCLC"
"C2" = "NSCLC|Squamous"
"C3" = "NSCLC|Non-Squamous"
"C4" = "HNSCC"
"C5" = "ESCC"
"C6" = "GEJ";

"CZZ" = "Total";

;
quit;

run;

[s_lassen]

You put the labels into a format, good idea.

I think you can use SQL to get the labels as well, that way:

%global collab1 collab2 collab3 collab4 collab5 collab6 collab7;
proc sql noprint;
  select distinct name, cats(putc(name,'$chrt.'),'|(N=',symget('M_'!!name),')|n(%)')
  into :vars separated by ' ',:collab1-:collab7 trimmed
  from dictionary.columns
  where memname = 'FINAL' and libname ='WORK' and 
             prxmatch("m/C1|C2|C3|C4|C5|C6|CZZ/oi",strip(name))> 0
  order by varnum;
quit;

I added the libname to the WHERE clause, as you will have problems otherwise if you have a table called FINAL in another library.

I started by declaring the macro variables first, otherwise only the ones that are actually written to will be created.

You should then be able to call your macro like this:

%rtf(inds=final,
rowheadvars=rowhead1 rowhead2,
rowheadlabel=,
colvars=&vars,
collabl1=&Collab1,
collabl2=&Collab2,
collabl3=&collab3,
...etc up to collabl7=&Collab7

If the macro is your own, you can also put the SQL call to get the variables inside the macro, and drop the parameters; the SQL call should just be adjusted to get the variable names from the right table, depending on the INDS parameter.

You do not have to write a macro to get the word count from a list, just use %sysfunc(Countw(&vars)). Or you can get the number of rows read from dictionary.columns by looking at the automatic macro variable SQLOBS.

[PaigeMiller]

You haven't shown us the code inside %RTF

[Tom]

Why does the macro call have only one parameter to supply the columns (names?) but many separate parameters to supply the headers?  Why not do the same thing for the headers?  Just use some other delimiter character, like a back slash.

%rtf
(inds=final
,rowheadvars=rowhead1 rowhead2
,rowheadlabel=
,colvars=C1 C2 C3 C5 
,collabels
=SCLC|(N=&m_C1)|n(%)
\NSCLC|Squamous|(N=&m_C2)|n(%)
\ESCC|(N=&m_C3)|n(%)
\GEJ|(N=&m_C6)|n(%)
....
);