topic Re: Combine observations to get count distinct values by group in SAS Programming

Combine observations to get count distinct values by group

Mariloud — Thu, 21 Jul 2022 02:21:18 GMT

I am struck on this dataset needing help with combining observations of category variable so that I can get the count of distinct values by group. Thanks for your help!

Re: Combine observations to get count distinct values by group

ballardw — Thu, 21 Jul 2022 03:01:03 GMT

An example of data and what you mean by "combine".

Possibly SQL is not the best approach depending on the data and what you mean by "combined". Proc freq and a custom format may be easier depending on what/how combining things.

Re: Combine observations to get count distinct values by group

Mariloud — Thu, 21 Jul 2022 03:37:24 GMT

For example the dataset contains the following values:

Category Category_count

Animals 85

culture 37

education 33

Animals 1653

Culture 1501

Education 1278

so on...............

I want to be able to count distinct value by category group like this👇

Category Category_count

Animals 1738

Culture 1538

Education 1311

Re: Combine observations to get count distinct values by group

ballardw — Thu, 21 Jul 2022 03:56:51 GMT

Do your values actually have differences of spelling such as "Education" and "education", "Culture" and "culture" or is that just bad typing?

If your need is to combine the spelling then Propcase(category) instead of just category may be the key.

Since you said "distinct" did you expect an SQL solution?

Re: Combine observations to get count distinct values by group

Mariloud — Thu, 21 Jul 2022 04:07:42 GMT

They actually do have differences in spelling such a "Animals" and "animals"

Re: Combine observations to get count distinct values by group

andreas_lds — Thu, 21 Jul 2022 04:58:25 GMT

@Mariloud wrote:

They actually do have differences in spelling such a "Animals" and "animals"

You need to fix that problem first

data cleaned;
   set have;
   Category = propcase(Category);
run;

proc summary data=cleaned nway;
   class Category;
   var Category_Count;
   output out=want(drop= _type_ _freq_) sum=;
run;

Instead of propcase using upcase or lowcase works, too.

Re: Combine observations to get count distinct values by group

s_lassen — Thu, 21 Jul 2022 08:47:51 GMT

Please post your example data as a data step, e.g. like this:

data have;
  input Category : $12. Category_count;
cards;
Animals         85
culture           37
education       33
Animals      1653
Culture        1501
Education    1278
;run;

You can get the sums using PROC SUMMARY:

proc summary data=have nway;
  class Category;
  format Category $upcase12.;
  var Category_count;
  output out=want sum=;
run;