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

[Sas_user987]

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

[PeterClemmensen]

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

[Sas_user987]

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

[ballardw]

---

@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-dat... 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.

[gamotte]

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.

[Reeza]

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

---

[Sas_user987]

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,

J

P.S. sas eg 8.1

[ballardw]

---

@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.

[Sas_user987]

Hi,

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

Thanks

[Tom]

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.

[Sas_user987]

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).

[Tom]

---

@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.

[Tom]

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-so...

To see what version check the automatic macro variable SYSVLONG.

87    %put &=sysvlong;
SYSVLONG=9.04.01M5P091317

[Tom]

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;

[Tom · Accepted solution]

---

@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;