topic Re: Distinct values for all columns in dataset in SAS Programming

Distinct values for all columns in dataset

PetePatel — Tue, 29 Jan 2019 11:22:37 GMT

Hi,

I have a large dataset (3m records) with around 10,000 columns (variables).

I need to find the number of distinct values in each column as shown below:

From this:

To this:

What is the most efficient way of generating these results for c. 10,000 columns?

Cheers

Sathish_jammy — Tue, 29 Jan 2019 12:27:27 GMT

Try the below code to get the distinct value for the variables...

Proc sql;
select count(ID)as ID, count(Name)as Name, count(Num) as Num from dataset_name;
Quit;

PetePatel — Tue, 29 Jan 2019 13:22:33 GMT

Thanks, is there a quicker way than having a really long script with 10,000 vars?

Ksharp — Tue, 29 Jan 2019 13:42:41 GMT

ods select none;
ods output nlevels=want;
proc freq data=sashelp.class nlevels ;
table _all_/missing;
run;
ods select all;

jeffharris — Wed, 01 Apr 2020 17:32:38 GMT

Why does this work?

Ksharp — Thu, 02 Apr 2020 11:15:46 GMT

Did you run the code and check WANT table ?

Var NLevels
Name 19
Sex 2
Age 6
Height 17
Weight 15