Loop to run PROC FREQ of each variable of a large dataset

[NewUsrStat]

Hi guys, 

suppose to have a sas dataset of 51 rows and 50 columns. 

I would like to do a PROC FREQ of each of the 50 columns. No combinations or something like that but only PROC FREQ and out with the prefix "stats" and then the name of the variable (column). 

Columns (variables) have different names but all starts with Q17_

Is there a way to not to do this variable by variable? 

Thank you in advance

[PaigeMiller]

---

@NewUsrStat wrote:

Hi guys, 

suppose to have a sas dataset of 51 rows and 50 columns. 

I would like to do a PROC FREQ of each of the 50 columns. No combinations or something like that but only PROC FREQ and out with the prefix "stats" and then the name of the variable (column). 

Columns (variables) have different names but all starts with Q17_

Is there a way to not to do this variable by variable? 

---

Better you should ask how to run PROC FREQ on each variable, rather than specifying a specific solution (in this case you specified you want a loop). No need for loop at all.

proc freq data=yourdatasetname;
    ods output onewayfreqs=onewayfreqs;
    tables q17_:;
run;

Then this code will help re-arrange the output data set into something that might be more usable: Re: proc freq one way table for multiple vars- export to data set - SAS Support Communities

[NewUsrStat]

Thank you very much for your support but I need to do the following for each variable: 

proc freq data = mydata;
	table Q17_test14 / out = stats_Q17_test14(drop = percent); 
run;

Here only one variable is shown. As you can see the prefix is "Q17_" and the prefix of the output is "stats_". 

I cannot put all the out tables together because I need to do additional calculation on a subset of them.

[PaigeMiller]

---

@NewUsrStat wrote:

Thank you very much for your support but I need to do the following for each variable: 

proc freq data = mydata;
	table Q17_test14 / out = stats_Q17_test14(drop = percent); 
run;

Here only one variable is shown. As you can see the prefix is "Q17_" and the prefix of the output is "stats_". 

I cannot put all the out tables together because I need to do additional calculation on a subset of them.

---

I have to disagree. You can take the output data set from the code I provided and then perform tasks on just a part of the data set.

[Kathryn_SAS · Accepted solution]

Here is a macro way:

%macro loop;
proc sql noprint;
 select name into :varlist separated by ' '
 from dictionary.columns
 where libname='WORK' and memname='MYDATA' /* must be uppercase */
 and upcase(name) like 'Q17_%';
quit;
%put &varlist;
%let count=&sqlobs;
%put &count;

proc freq data=mydata;
%do i=1 %to &count;
 tables %scan(&varlist,&i,%str( )) / out = stats_%scan(&varlist,&i,%str( ))(drop = percent); 
%end;
run;

%mend;
options mprint mlogic symbolgen;
%loop

[Tom]

You should probably take a step back and ask why you want to make so many datasets.

If you want to generate code first figure out what code you want to generate.  Perhaps something like:

proc freq data=mydata;
   tables myvar / noprint out=stats_myvar ;
run;

So then to do that first get a list of the variables you want.

proc contents data=mydata noprint out=contents; run;

Then you could use that list to generate those statements.

filename code temp;
data _null_;
  file code;
  set contents;
  length nliteral $65 dsname $32 ;
  nliteral = nliteral(_name_);
  dsname = cats('stats_',compress(_name_,'_','kad'));
  put 'tables ' nliteral '/ noprint out=' dsname ';' ;
run;

Then you could use %INCLUDE to run those generated statements.

proc freq data=mydata;
%include code / source2;
run;

[FreelanceReinh]

Hi @NewUsrStat,

You can also use the MATCH_ALL option available in the ODS OUTPUT statement:

/* Create sample data for demonstration */

data have;
call streaminit(27182818);
array a[5] Q17_test Q17_new Q17_tmp Q17_abc Q17_314;
do i=1 to 51;
  do _n_=1 to dim(a);
    a[_n_]=rand('integer',9);
  end;
  output;
end;
run;

/* Create datasets _TMP1, _TMP2, ... containing a frequency table for each "Q17_" variable */

ods output onewayfreqs(match_all)=_tmp1(drop=Table F_: Cum: Percent rename=(Frequency=COUNT));
proc freq data=have;
tables Q17_:;
run;

/* Compile pairs of dataset names and prefixed variable names */

proc sql noprint;
select cats('_tmp',varnum,'=stats_',name) into :chg_list separated by ' '
from dictionary.columns
where libname='WORK' & memname='HAVE' & upcase(name) eqt 'Q17_';
quit;

/* Rename the ODS output datasets to STATS_<variable name> */

proc datasets lib=work nolist;
change &chg_list;
quit;

[Ksharp]

Here is my SQL solution.

/* Create sample data for demonstration */

data have;
call streaminit(27182818);
array a[5] Q17_test Q17_new Q17_tmp Q17_abc Q17_314;
do i=1 to 51;
  do _n_=1 to dim(a);
    a[_n_]=rand('integer',9);
  end;
  output;
end;
run;


data _null_;
 set sashelp.vcolumn(where=(libname='WORK' and memname='HAVE' and upcase(name)=:'Q17_')) end=last;
 if _n_=1 then call execute('proc sql;');
 call execute(cat('create table stats_',strip(name),' as select ',name,',count(*) as count from have group by ',name,';'));
 if last then call execute('quit;');
run;