topic Re: Count different columns in SAS Programming

Count different columns

SASPhile — Fri, 04 Oct 2013 03:30:24 GMT

How to get the distinct count of id and acct no as 3 and 5 in a single query? Id Acct no

1146010	87158160940
1146010	77269250216
4248019	47268006730
4413015	99154430103
4413015	88726577735

Re: Count different columns

Jagadishkatam — Fri, 04 Oct 2013 06:56:28 GMT

Please try the below code

data have;

input id Acct_no;

cards;

1146010 87158160940

1146010 77269250216

4248019 47268006730

4413015 99154430103

4413015 88726577735

;

run;

proc sql;

create table want as select count(distinct id) as id_count, count(distinct Acct_no) as Acct_no_count from have;

quit;

Thanks,

Jagadish

Re: Count different columns

Keith — Fri, 04 Oct 2013 09:38:04 GMT

ods select nlevels;

proc freq data=have nlevels;

run;

Re: Count different columns

yaswanthj — Fri, 04 Oct 2013 14:02:28 GMT

Is it possible using DATA STEP.. same result as proc sql and proc freq???

Re: Count different columns

Keith — Fri, 04 Oct 2013 14:26:21 GMT

Technically yes, but it will be messy to code. Why do you ask when there are 2 much simpler solutions on offer?

Re: Count different columns

Haikuo — Fri, 04 Oct 2013 14:38:11 GMT

Here is a Hash() approach that is technically producing the same outcome as Proc SQL in ONE step/query:

data want;

keep id_ct acct_ct;

if 0 then set have;

declare hash acct(dataset:'have');

acct.definekey('acct_no');

acct.definedone();

declare hash i(dataset:'have');

i.definekey('id');

i.definedone();

id_ct=i.num_items;

acct_ct=acct.num_items;

output;

stop;

run;

Haikuo

Re: Count different columns

JasonXin — Sat, 05 Oct 2013 22:12:29 GMT

In current SAS High Performance (HP), one common foundation HP PROC is HPDMDB where you can code like this

proc hpdmdb data=&outdsn. classout=outdsn varout=v maxlevel=15000000; /*if you set a small #, it wraps the rest into OTHERS*/

class _all_ ; /*list all the variables in the data set including interval, numeric as well as categorical*/

run ;

The result will show % of each unique value and their levels. For very continuous variables, it does indeed appear very miscellaneous.

If you have Enterprise Miner license, you should be able to use proc DMDB. The difference is when the data set gets big, or when the table does not have too many observations, but you have many columns to count, then it may take some time. Proc HPDMDB runs much faster. It leverages multi-threading capabilities on your computers. If you are set up to run on parallel nodes with in-memory, it will be much, much faster.