topic Re: how can I do the same action for all variables in the dataset in SAS Programming

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

Sas_user987 — Thu, 29 Oct 2020 14:23:45 GMT

Hello,

I would like your advice on how to deal with this as I cant figure this our my self.

I currently have around 600 variables in SAS table. What I want to do is to show unique value in each column and proportion of each value in the column (whether its number of rows, exposure or any other way of measuring).

In python I would simply add all columns in to the list and then do the "for" loop, asking python to do the same action for every i in the list.

How could I achieve the same in SAS?

Just to be clear, I am looking for a way to do the same action for every variable in SAS table.

Many thanks,

P.S. Im using SAS 8.1

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

PeterClemmensen — Thu, 29 Oct 2020 14:31:22 GMT

Do you want this in a report of some sort or in a SAS data set?

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

Sas_user987 — Thu, 29 Oct 2020 14:33:49 GMT

I will have to export this into Excel worksheet later on, so no report needed at this stage

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

ballardw — Thu, 29 Oct 2020 15:25:35 GMT

@Sas_user987 wrote:

Hello,

I would like your advice on how to deal with this as I cant figure this our my self.

I currently have around 600 variables in SAS table. What I want to do is to show unique value in each column and proportion of each value in the column (whether its number of rows, exposure or any other way of measuring).

In python I would simply add all columns in to the list and then do the "for" loop, asking python to do the same action for every i in the list.

How could I achieve the same in SAS?

Just to be clear, I am looking for a way to do the same action for every variable in SAS table.

Many thanks,

J

P.S. Im using SAS 8.1

Really, you need to provide some example data, probably 10 rows and 5 variables would suffice. Then show the expected results for that output.

If you don't know how to write a data step from data:

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Your requirement of "What I want to do is to show unique value in each column and proportion of each value in the column " is actually much easier in one of the report procedures but what you expect for appearance is critical.

The approach that SAS uses for manually written processes for "each variable" would be an array but you cannot mix variable types in an array. So actual content matters and desired appearance is critical.

Or perhaps you want:

Proc freq data=yourdatasetname;
run;

Which will create a separate table for each variable with the count, percent of records, cumulative count and cumulative percent plus and indicator for the number of missing values.

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

gamotte — Thu, 29 Oct 2020 15:02:38 GMT

Hello,

To perform the same action on all columns of a dataset, you can use call vnext in a loop

as follows :

%macro dosomething(varname);

    variable=&varname.;
    value=vvaluex(&varname.); 
    put _N_= variable= value=;

%mend dosomething;

data _NULL_;
    set sashelp.class;
    length _NAME_ $32.;

    do while(_NAME_ ne "_NAME_");
        call vnext(_NAME_);
        if _NAME_ ne "_NAME_" then do;
            %dosomething(_NAME_);
        end;
    end;
run;

You can also use arrays but you will have to duplicate your loop if the dataset

contains character as well as numeric columns.

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

Reeza — Thu, 29 Oct 2020 15:20:19 GMT

PROC FREQ piped to ODS EXCEL is the simplest method, each variable goes to it's own worksheet.

ods excel file = "/home/fkhurshed/example.xlsx" options(sheet_interval = "table");

proc freq data=sashelp.class;
run;

ods excel close;

FYI - you're likely not on SAS 8.1 (it's over 20+ years old) but on EG 8.1? You can find your SAS version by running the following and checking the log afterwards.

proc product_status;run;

@Sas_user987 wrote:

Hello,

I would like your advice on how to deal with this as I cant figure this our my self.

I currently have around 600 variables in SAS table. What I want to do is to show unique value in each column and proportion of each value in the column (whether its number of rows, exposure or any other way of measuring).

In python I would simply add all columns in to the list and then do the "for" loop, asking python to do the same action for every i in the list.

How could I achieve the same in SAS?

Just to be clear, I am looking for a way to do the same action for every variable in SAS table.

Many thanks,

J

P.S. Im using SAS 8.1

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

Sas_user987 — Thu, 29 Oct 2020 16:55:06 GMT

Thanks everyone for your responds.

So currently I have:

proc summary data=datatouse nway;
class varname1;
var measurevar;
output out=varname1 (drop=_FREQ_ _TYPE_ rename=(measurevar=ex_varname1)) sum=;
run;

the reason im using proc summary instead of proc freq is so i could use exposure as a measuring variable.

The idea later is to have one excel sheet with all variables which would allow for quick search later on.

varname1	ex_varname1	varname2	ex_varname2
unique_val1	100	unique_val1	200
unique_val2	150	unique_val2	150
missing	200	missing	100

I would usually write a macro for this and call a macro for each variable, but when there are 600 variables its hard to understand what is happening in the SAS file itself.

I want SAS automatically perform summary to every variable in the dataset without specifically naming it, so i do need to find a way to change varname1 to varname2 etc.

I will later need to take all these var names and stick it into one big table. Hence I was thinking about something similar to python lists, as I will need var names in later stages.

Thanks,

P.S. sas eg 8.1

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

Tom — Thu, 29 Oct 2020 16:07:14 GMT

I doubt you are using 8.1, that was released 20 years ago.

https://blogs.sas.com/content/iml/2013/08/02/how-old-is-your-version-of-sas-release-dates-for-sas-software.html

To see what version check the automatic macro variable SYSVLONG.

87    %put &=sysvlong;
SYSVLONG=9.04.01M5P091317

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

Tom — Thu, 29 Oct 2020 16:09:56 GMT

You can use the special _ALL_ variable list.

So if your existing dataset is named HAVE you could use this code to produce frequency tables for every variable.

proc freq data=have;
  tables _all_;
run;

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

ballardw — Thu, 29 Oct 2020 16:40:03 GMT

@Sas_user987 wrote:

Thanks everyone for your responds.

So currently I have:
proc summary data=datatouse nway;
class varname1;
var measurevar;
output out=varname1 (drop=_FREQ_ _TYPE_ rename=(exposure=ex_varname1)) sum=;
run;
the reason im using proc summary instead of proc freq is so i could use exposure as a measuring variable.

Makes no sense. You aren't using "exposure" anywhere in the body of the proc summary code so it does not exist for output and no "ex_varname1" would be created but you will get a warning

WARNING: The variable exposure in the DROP, KEEP, or RENAME
         list has never been referenced.

And to tell the truth, the appearance of the desired output does not appear to be "easy to search" in any form.

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

Sas_user987 — Thu, 29 Oct 2020 16:56:48 GMT

Hi,

I edited my code to have measurevar instead of exposure. Hopefully makes more sense now.

Thanks

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

Tom — Thu, 29 Oct 2020 17:17:36 GMT

Why do you need the RENAME= option? Without that your code runs fine. Just list the variables you want in the VAR statement.

proc summary data=datatouse nway;
  class varname1;
  var _numeric_;
  output out=varname1 (drop=_FREQ_ _TYPE_) sum=;
run;

If you must have different names for the new variables use the /AUTONAME option in the OUTPUT statement.

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

Reeza — Thu, 29 Oct 2020 17:26:45 GMT

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

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

Tom — Thu, 29 Oct 2020 17:27:03 GMT

@Tom wrote:

You can use the special _ALL_ variable list.

So if your existing dataset is named HAVE you could use this code to produce frequency tables for every variable.
proc freq data=have;
  tables _all_;
run;

If you need the counts be the "sum" of some other variable use the WEIGHT statment.

proc freq data=have;
  tables varname1-varname600;
  weight measurement;
run;

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

Sas_user987 — Thu, 29 Oct 2020 17:27:19 GMT

in this case I am using same measuring variable to measure varname1, varname2 etc. If i dont change measurevar into ex_varname1, ex_varname2, in later stages when I want to stick everything into one big table later on, I would end up with same var name (the measurevar).

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

Tom — Thu, 29 Oct 2020 17:28:51 GMT

@Sas_user987 wrote:
in this case I am using same measuring variable to measure varname1, varname2 etc. If i dont change measurevar into ex_varname1, ex_varname2, in later stages when I want to stick everything into one big table later on, I would end up with same var name (the measurevar).

Sounds like you want to use measurevar as a WEIGHT. See my example using PROC FREQ.

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

Ksharp — Fri, 30 Oct 2020 12:55:28 GMT

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;

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

Sas_user987 — Fri, 30 Oct 2020 15:25:38 GMT

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,

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

Reeza — Fri, 30 Oct 2020 15:37:19 GMT

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

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

Tom — Fri, 30 Oct 2020 16:33:14 GMT

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;