how can I do the same action for all variables in the dataset

[Reeza]

Are you trying to do something like this:
https://communities.sas.com/t5/SAS-Communities-Library/Demographic-Table-and-Subgroup-Summary-Macro-...

[Ksharp]

What you are looking for is IML code .

proc iml;
use sashelp.heart nobs nobs;
read all var _char_ into x[c=vname];
close;
do i=1 to ncol(x);
  call tabulate(level,freq,x[,i]);
  level=t(level);
  freq=t(freq);
  percent=freq/nobs;
  print level[c=(vname[i]) l=''] freq percent[f=percent8.2];
end;
quit;

[Sas_user987]

Hello,

Thanks for this, this looks very advanced. I tried your solution, but it doesn't work and I can not fix it because I don't really understand it.

After whole day of research and testing, I finally ended up somewhere (but there's still a long way to go at this pace).

First of all I run proc contents  to get the variable names:

proc contents data=have1 noprint out=col_name (keep=name); run;

I then run the following statement to put those vars in some kind of macro i guess? 

Proc sql;
select * into :vars_to_run from col_name;
quit;

And finally i get to the stage where I could calculate proc freq for every var in the dataset

%macro test;
    %do i=1 %to %sysfunc(countw(&variables_to_run));
    proc freq data=have1;
    tables var_name / norow nocol nopercent nocum missing out=var_name;
    weight measurevar;
    %end;
    run;
%mend;
%test;

var_name - system should automatically select all available variables in the dataset

I just need to find a way of telling SAS to run this for every var in the dataset and output as a separate dataset with var_name as a table name.

Thanks,

J

[Reeza]

You do know that _all_ refers to all variables? It has specific uses but works for what you need.

   ods output onewayfreq = want;
    proc freq data=have1;
    tables _all_ / norow nocol nopercent nocum missing ;
    weight measurevar;
    run;

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

An alternative option is this which does it all at once, no macro and pipes them all to a single data set. Change 

*Run frequency for tables;
ods table onewayfreqs=temp;
proc freq data=sashelp.class;
	table _all_;
weight age;
run;

*Format output;
data want;
length variable $32. variable_value $50.;
set temp;
Variable=scan(table, 2);

Variable_Value=strip(trim(vvaluex(variable)));

keep variable variable_value frequency percent cum:;
label variable='Variable' 
	variable_value='Variable Value';
run;

*Display;
proc print data=want(obs=20) label;
run;

---

@Sas_user987 wrote:

Hello,

Thanks for this, this looks very advanced. I tried your solution, but it doesn't work and I can not fix it because I don't really understand it.

After whole day of research and testing, I finally ended up somewhere (but there's still a long way to go at this pace).

First of all I run proc contents  to get the variable names:

proc contents data=have1 noprint out=col_name (keep=name); run;

I then run the following statement to put those vars in some kind of macro i guess? 

Proc sql;
select * into :vars_to_run from col_name;
quit;

And finally i get to the stage where I could calculate proc freq for every var in the dataset

%macro test;
    %do i=1 %to %sysfunc(countw(&variables_to_run));
    proc freq data=have1;
    tables var_name / norow nocol nopercent nocum missing out=var_name;
    weight measurevar;
    %end;
    run;
%mend;
%test;

var_name - system should automatically select all available variables in the dataset

I just need to find a way of telling SAS to run this for every var in the dataset and output as a separate dataset with var_name as a table name.

Thanks,

J

---

[Sas_user987]

This actually worked, and finally got some results. 

Could this be rearrange differently?

Currently I have 

Variable	Variable value	Frequency
var1	A	1
var1	B	1
var1	C	1.5
var2	A	1
var2	B	2
var3	C	3
var3	D	1

There are many continuous variables with many unique values and I am worried that since this have to be exported to Excel to share with managers etc, I will hit Excel limits in terms of rows.

Would it be possible to rearrange the table to look like below?

Var1	var1_measure	var2	var2_measure	var3	var3_measure
A	1	A	1	C	3
B	1	B	2	D	1
C	1.5

Thanks,

J

[Reeza]

Personally for continuous variables, a frequency report wouldn't be considered useful. Usually for continuous variables you instead report summary statistics. One of the first links I provided had a link to a demo summary macro that would let you identify the type of variable (continuous/categorical etc.) and it generated appropriate summaries. That's the usual method. Obviously you need to do what's right for your company though. You could also, manually, add in a filter to only show the top 10 or 20 results if there are too many.

[Sas_user987]

The final table I try to produce is a piece of a bigger decision making picture and its not really related to frequency. All we care about is weight that each unique value has in the variable column.

We have mixed data in the table and instead of picking continuous variables by hand I just want to just go for it. This code will have to run through out the night anyway. The final table then is told to keep only 1000 obs.

I made this SAS script previously but it was quite manual as I had to write 600 %let statements and then call those 600 %let multiple times. Therefore the goal here is to make it work automatically. Take all the variables that exist in any dataset we use and produce the table I need.

[Reeza]

The program I provided does that. You would only need to change the name of the data set which could be changed to a macro by wrapping it in a macro.

[Tom]

Not sure why want to complicate your program instead of just using the existing functionality of SAS.  Why would you want to read through the input datasets over and over again instead of generating all of the data in one pass? Why would you want to generate a separate dataset for each input variable?

Another way to think about how to something for every variable is to restructure the data.  So if you have a unique id you could just use PROC TRANSPOSE.

proc transpose data=have1 
  out=tall(rename=(col1=VALUE)
           where=(upcase(_name_) not in ('ID','MEASUREVAR')
          ) 
;
  by ID MEASUREVAR;
  var _all_;
run;
proc freq data=tall;
  tables _NAME_*VALUE / norow nocol nopercent nocum missing out=WANT;
  weight measurevar;
run;

Note this might not work so well with a lot of distinct values.  So it might help to sort the TALL dataset first and then use BY processing.

proc sort data=tall;
  by _name_;
run;
proc freq data=tall;
  by _name_;
  tables VALUE / norow nocol nopercent nocum missing out=WANT;
  weight measurevar;
run;

It does not sound like you know enough about what SAS code you want to run to begin trying to use macro code to generate SAS code.  But if you did want to build a macro to do something for every variable in a dataset then here are some pointers. First make sure to parameterize your macro properly.  In this case I would make the name of the DATASET the parameter to the macro and possibly also the name of the variable being summed.  Second if you use the method of sticking variable names into a single macro variable remember that macro variables have a limit of 64k bytes so there will be a limit to the number of variables the macro could handle. So if the variable names are all 31 characters long it could fit 2K variable names. If they are all 7 characters long then it could fit 8K variable names. 

The specific mistakes your current code has are:

You only put one name into the macro variable.  And you should eliminate MEASUREVAR from the list.

select name into :vars_to_run separated by ' ' from col_name
  where upcase(name) ne 'MEASUREVAR'
;

You never actually pull out the next name from the list and use it to generate the code you want to run.

%do i=1 %to %sysfunc(countw(&variables_to_run));
proc freq data=have1;
  %let var=%scan(&variables_to_run,&i,%str( ));
  tables &var  / norow nocol nopercent nocum missing out=&var;
  weight measurevar;
%end;
run;

[Sas_user987]

I am not an experienced SAS user, I know what the final result should look like and I build a road map of steps on how can I get there. My code that I have written so far is how my brain works. Im sure there are plenty of solutions to get to the desired result, but I guess this is something that will come with experience.

And yes, my code does not pull another variable to do the calculation. I tried your suggested line %let var=%scan(&variables_to_run, &i, %str()); but there seem to be a mistake somewhere as it still calculates the first var in the list.

[Tom]

The way to learn is to do.  Open interactive SAS (or if you have to use SAS/Studio or Enterprise Guide to a slightly less interactive session) and play around. Try this macro program:

%macro test;
%local namelist n i name ;
proc sql noprint;
select name into :namelist separated by ' ' from sashelp.class;
%let n=&sqlobs;
quit;
%do i=1 %to &n ;
  %let name=%scan(&namelist,&i,%str( ));
  %put &=i &=name;
%end;
%mend test;
%test;

If you are not comfortable with macro code perhaps it would be easier to generate the code using a data step instead.

filename code temp;
data _null_;
  set sashelp.class ;
  file code;
  put '%put ' _n_= name= ';' ;
run;
%include code / source2;

This has the big advantage that it is just a text file. So when debugging the code you can look at the file. If you are doing it interactively you can open the file in the editor and highlight a few of the lines of generated code and run them.