topic Re: Counting Distinct Entries by Variables in SAS Procedures

Counting Distinct Entries by Variables

1800bigk — Fri, 23 Sep 2011 17:29:33 GMT

I am having problems counting things in SAS. I have a data set with 3 vars: Level1, Level2 and FeatureNbr. I want to be able to count the number of DISTINCT FeatureNbrs for each possible value of Level1 and Level2.

SAMPLE DATA
Level1	Level2	FeatureNbr
Ops	SVCS	23654
Ops	Forms	23654
Ops	Supplies	23654
Ops	Other	23654
People	Salaries	23654
People	Fleet	27589
People	Benefits	27589
People	Other	27589
IT	Equipment	27589
IT	Programming	27589
IT	Phone	36258
Infrastructure	Equipment	36258
Infrastructure	Real Estate	36258
Infrastructure	Other	36258

My normal way to count is with proc summary but that counts each row so I found some proc sql code that does a select count(distinct(featureNbr)) on my data but it just returns one number.

ANy help would be greatly appreciated.

Re: Counting Distinct Entries by Variables

FriedEgg — Fri, 23 Sep 2011 17:32:44 GMT

proc sql;

select level1, level2, count(distinct(featurenbr)) as count

from sample_data

group by level1, level2

quit;

Re: Counting Distinct Entries by Variables

art297 — Fri, 23 Sep 2011 17:47:15 GMT

But I am concerned why you stated that you couldn't use proc summary. In your sample data, all of the counts will equal 1 by definition. In the following, I added an extra data line so that one of the combinations would result in a value of 2:

data have;

informat Level1 $15.;

informat Level2 $15.;

input Level1 Level2 & FeatureNbr;

cards;

Ops SVCS 23654

Ops Forms 23654

Ops Supplies 23654

Ops Other 23654

People Salaries 23654

People Fleet 27589

People Benefits 27589

People Other 27589

IT Equipment 27589

IT Programming 27589

IT Phone 36258

Infrastructure Equipment 36258

Infrastructure Real Estate 36258

Infrastructure Other 36258

;

proc summary data=have nway;

var FeatureNbr;

class level1 level2;

output out=want (drop=_:) n=;

run;

Counting Distinct Entries by Variables

1800bigk — Fri, 23 Sep 2011 18:16:50 GMT

When I did a proc summary to count it was double counting because it would count each row as a new feature but as you can see there are only 3 distinct features in my sample. I had to re login to the sas server and re load my data so I haven't tired anything yet, thanks for the input.

Counting Distinct Entries by Variables

Tom — Fri, 23 Sep 2011 18:27:25 GMT

What do you want as the output? You were concerned about getting only one number, but then you say that you only want one number for the variable FEATURE. If you look for distinct values of the variable FEATURE nested within the other two variables (see code above from FriedEgg) then only one combination has more than one distinct value of feature.

If you want the number of distinct levels for each variable then try PROC FREQ with the NLEVELS option. That should tell you that the variable FEATURE has three distinct values.

Counting Distinct Entries by Variables

art297 — Fri, 23 Sep 2011 18:29:09 GMT

I misunderstood what you were seeking and may still not understand your request.

I would go with FriedEgg's suggested proc sql code, but make sure that you add a semicolon before the quit statement:

proc sql;

select level1, level2,

count(distinct(featurenbr)) as count

from have

group by level1, level2

;

quit;

However, that will give you a count of 1 for each unique level1*level2*featurenbr combination in your data.

Counting Distinct Entries by Variables

1800bigk — Fri, 23 Sep 2011 18:39:53 GMT

I want to be able to report out on the number of unique features that occured for any given variable in my data. I am begienning to realize that this is much more difficult that I thought. I have 41 variables and zillions of rows and my boss would like me to get the data to a point where we can see how many unique features roll up to any variable in my data. So even though there is 7gb worth of rows there are only a few thousand possible places that a feature could roll up to. It looks like I will be doing 41 different proc sql like mentioned above so I can get counts for all my variables.

Counting Distinct Entries by Variables

art297 — Fri, 23 Sep 2011 18:46:21 GMT

I think we are all miscommunicating. What output would you expect to see from your sample data?

Re: Counting Distinct Entries by Variables

1800bigk — Fri, 23 Sep 2011 18:55:30 GMT

Something along the lines of

Level2	Count of Distinct Features	Level1	Count of Distinct Features
OPS	1	Benefits	1
People	2	Equipment	2
IT	2	Fleet	1
Infrastructure	1	Forms	1
Other	3
Phone	1
Programming	1
Real Estate	1
Salaries	1
Supplies	1
SVCS	1

Re: Counting Distinct Entries by Variables

art297 — Fri, 23 Sep 2011 19:02:45 GMT

Still not sure, but possibly something like?:

proc sql;

select level1,

count(distinct(featurenbr)) as count

from have

group by level1

;

select level2,

count(distinct(featurenbr)) as count

from have

group by level2

;

quit;

Counting Distinct Entries by Variables

FriedEgg — Fri, 23 Sep 2011 20:59:19 GMT

data have;

infile cards dsd dlm='09'x;

input (level1-level2) (:$20.) featurenbr;

cards;

Ops SVCS 23654

Ops Forms 23654

Ops Supplies 23654

Ops Other 23654

People Salaries 23654

People Fleet 27589

People Benefits 27589

People Other 27589

IT Equipment 27589

IT Programming 27589

IT Phone 36258

Infrastructure Equipment 36258

Infrastructure Real Estate 36258

Infrastructure Other 36258

Ops SVCS 23654

Ops Forms 23654

Ops Supplies 23654

Ops Other 23654

People Salaries 23654

People Fleet 27589

People Benefits 27589

People Other 27589

IT Equipment 27589

IT Programming 27589

IT Phone 36258

Infrastructure Equipment 36258

Infrastructure Real Estate 36258

Infrastructure Other 36258

Ops SVCS 23654

Ops Forms 23654

Ops Supplies 23654

Ops Other 23654

People Salaries 23654

People Fleet 27589

People Benefits 27589

People Other 27589

IT Equipment 27589

IT Programming 27589

IT Phone 36258

Infrastructure Equipment 36258

Infrastructure Real Estate 36258

Infrastructure Other 36258

Ops SVCS 23654

Ops Forms 23654

Ops Supplies 23654

Ops Other 23654

People Salaries 23654

People Fleet 27589

People Benefits 27589

People Other 27589

IT Equipment 27589

IT Programming 27589

IT Phone 36258

Infrastructure Equipment 36258

Infrastructure Real Estate 36258

Infrastructure Other 36258

Infrastructure Equipment 27589

Infrastructure Real Estate 27589

Infrastructure Other 27589

;

run;

proc sql;

create view vhave as

select distinct featurenbr, level1, level2

from have;

quit;

proc summary data=vhave;

var featurenbr;

class level2 level1;

output out=want(rename=(_type_=level) where=(level>0) drop=_freq_) n=count;

run;

data level1(drop=level2) level2(drop=level1) cross;

set want;

select(level);

when(1) output level1;

when(2) output level2;

otherwise output cross;

end;

drop level;

run;

Re: Counting Distinct Entries by Variables

DLing — Fri, 23 Sep 2011 20:59:54 GMT

Summarize twice:

proc summary data=have missing nway;

class level1 feature;

output out=temp(drop=_:) n=;

run;

proc freq data=temp;

table level1;

run;

Repeat again for level2. Clumsy but works and generates additional data that may be useful later.

Re: Counting Distinct Entries by Variables

Tom — Sun, 25 Sep 2011 21:29:29 GMT

You seem to want to count the number of distinct values for a single variable (&COUNTVAR) when crossed with each of a number of other variables (&VARLIST). You can use the TYPES statement in PROC SUMMARY to generate a dataset that will make it easy to get those counts using PROC FREQ. The only problem I see is when your variables have missing values.

%let varlist=level1 level2 ;

%let countvar=featurenbr ;

proc summary data=have nway ;

class &varlist &countvar;

types (&varlist)*&countvar ;

output out=want1 ;

run;

proc freq data=want1 ;

tables &varlist ;

run;

Counting Distinct Entries by Variables

Howles — Mon, 26 Sep 2011 13:23:48 GMT

Note that NWAY contradicts the specifications in the TYPES statement. Apparently SAS ignores NWAY in this situation.