SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to count how many times value in a table repeated ?

Reply
Occasional Contributor
Posts: 6

How to count how many times value in a table repeated ?

Hello Smiley Wink , I'd like to ask a question, since I'm trying to acquire result which I want, and don't know how. I have a table, and what I'd like to do, is to count how many times, value in a colums repeats. Where can I find such an option?

Let's say I have

Col1Col2Col3Col4
115g
124b
134b
247d

.. and what I want to do with that is that kind of result:

there are 3 col2 that can be assigned to the value of col1 ,

there is 4 - which is number repeated most of col3 - 4 have a value of b

edit. Is the function Frequencies can count this? (how many times some value repeated?)

Frequent Contributor
Posts: 89

Re: Counting positions in a table

COUNTC function ??

can you give some examples or your test data step ??

Herman

Respected Advisor
Posts: 3,799

Re: How to count how many times value in a table repeated ?

It is difficult to understand what you want but maybe something like this.

data a;
   input col1-col3 col4 $;
   cards;
1  1  5  g
1  2  4  b
1  3  4  b
2  4  7  d
;;;;
   run;
proc summary descend descendtypes chartype data=a;
   class col:;
   ways 1;
  
output out=counts;
   run;
proc print;
  
run;

1-12-2015 8-51-10 AM.png
Occasional Contributor
Posts: 6

Re: How to count how many times value in a table repeated ?

Posted in reply to data_null__

Ok I think now I will translate it better to you Smiley Happy

I have that kind of table ^

What I need is that kind of table, but in some way made in SAS

Super User
Super User
Posts: 7,942

Re: How to count how many times value in a table repeated ?

Well, they look like group bys to me:

data have;
  shiftid=3;
    employeeid=72;
    departmentid=15;
    name="Shipping and Recieving";
    output;
    employeeid=73;
    departmentid=7;
    name="Production";
    output;
run;

proc sql;
  create table WANT as
  select  A.*,
          B.*
  from    (select SHIFTID,count(distinct EMPLOYEEID) as EMPLOYEES from WORK.HAVE group by SHIFTID) A
  left join (select SHIFTID,DEPARTMENTID,NAME,count(distinct EMPLOYEEID) as NUM_EMPLOY from WORK.HAVE group by SHIFTID,DEPARTMENTID,NAME) B
  on      A.SHIFTID=B.SHIFTID;
quit;

Respected Advisor
Posts: 3,799

Re: How to count how many times value in a table repeated ?

Show data in data step with CARDS.  Not a picture.

Super User
Posts: 19,772

Re: How to count how many times value in a table repeated ?

You'll need to modify the variable and table names but the following is

proc sort data=table1;

by shiftID employee department;

run;

proc freq data=table1;

by shiftID;

table department;

run;

Super User
Posts: 5,499

Re: How to count how many times value in a table repeated ?

If you want something quick and dirty, you can use:

proc freq data=table1;

tables ShiftID * DepartmentID * Name / missing list out=counts;

run;

If you want something pretty, you'll have to take the output data set COUNTS and figure out how to print it.  It will already contain the data that you need, and it's just a matter of generating the proper report.

Good luck.

Ask a Question
Discussion stats
  • 7 replies
  • 1660 views
  • 0 likes
  • 6 in conversation