BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
How to get the distinct count of id and acct no as 3 and 5 in a single query? Id                 Acct no
114601087158160940
114601077269250216
424801947268006730
441301599154430103
441301588726577735
6 REPLIES 6
Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
Keith
Obsidian | Level 7

ods select nlevels;

proc freq data=have nlevels;

run;

yaswanthj
Calcite | Level 5

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

Keith
Obsidian | Level 7

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

Haikuo
Onyx | Level 15

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

JasonXin
SAS Employee

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1206 views
  • 2 likes
  • 6 in conversation