topic Re: Number of levels in character variables in SAS Programming

Number of levels in character variables

Ujjawal — Thu, 04 Jun 2015 20:33:09 GMT

I am trying to create a macro that calculates number of levels (distinct categories) for all the character variables in a dataset. Currently, i am using PROC SQL to calculate distinct categories. If i have 100 variables, i have to run PROC SQL 100 times in a loop. Is there a better way to do it? Is "stackods" kind of option available in PROC SQL so that i can reshape my dataset easily?

Sample Code -

proc sql;

create table abc as

select count(distinct(sex)) as sex_levels

from sashelp.class;

quit;

Sample Output -

Variable	N_Levels
Sex	2
Age	6

It's just the sample code and output for 2 variables. I need to do it for all the character variables in a dataset.

Note : All my categorical variables are stored as character variables (string) in my dataset.

Thanks in anticipation!

Re: Number of levels in character variables

Tom — Thu, 04 Jun 2015 21:26:39 GMT

PROC FREQ NLEVELS ....

Re: Number of levels in character variables

Ron_MacroMaven — Thu, 04 Jun 2015 22:15:18 GMT

I am with Tom: proc.freq.nlevels provides the answer, ... for the variables in the data-set.

I have written several papers on extrapolating from n-levels.

the information you are seeking is in contents output data set which has a variable type -- in (1:n, 2:c)

you have to sort both out-nlevels and out-contents by name in order to merge them.

check this SASware ballot item.

Re: Number of levels in character variables

Ron_MacroMaven — Fri, 05 Jun 2015 00:44:52 GMT

%let data = sashelp.class;

%let data = sashelp.heart;

proc freq data = &data

(keep = _character_)

nlevels;

ods exclude onewayfreqs;

ods output

nlevels = out_n_levels

(keep = tablevar nlevels

rename = (tablevar = name

nlevels = n_levels));

*to match out_contents;

proc print;

Re: Number of levels in character variables

devrant — Thu, 08 Feb 2018 14:27:46 GMT

How would you tackle a data set with more than 70k levels for at least one variable? (proc freq always yields OoM error.)