BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

Hi everyone,

 

I wanted to know whether there is a simple way to calculate the count of occurance of a particular value in a particular field from row 1 till the row getting processed?  Following is an example of what I need:

StatusCountifFormula
Y1=COUNTIF($A$2:$A2,A2)
Y2=COUNTIF($A$2:$A3,A3)
Y3=COUNTIF($A$2:$A4,A4)
X1=COUNTIF($A$2:$A5,A5)
Y4=COUNTIF($A$2:$A6,A6)
X2=COUNTIF($A$2:$A7,A7)
Y5=COUNTIF($A$2:$A8,A8)
1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

You want cumulative frequency, from record 1 through current record, of the current value of  status.  So let's assume you create dataset HAVE with the series of status values.

 

If you know in advance all the possible values of status, then this is short and simple.  It keeps an array of counts with one element per expected value of status.  Arrays with the _TEMPORARY_ description will have their values persist from record to record, but  will not be stored in the data set - a perfect place to keep running counts. The FIND function return the posiition in the first argument where the second argument is found.:

 

data have;
  input status :$1. @@;
datalines;
Y Y Y X Y X Y
run;

data want;
  array counts {2} _temporary_ (2*0);
  set have;
  index=find('XY',status);
  counts{index}+1;
  countif=counts{index};
  drop index;
run;

 

If you don't know in advance all the status values in have, then the thing to do is create a hash object, keyed on STATUS and storing the running countif value for each status, as below.

 

  1. The least intuitive thing about the hash object in this program is that it is declared only once overall (not once per observations).  That's why you see object COUNTS declared and defined in the "if _n_=1 then do" group.  "_N_=1" in this program is true only when the first observation is current.
  2. The FIND() method retrieves the current value of COUNTIF based on the status value.  If the status value is not already in the object, then FiND() fails, and yields a non-zero return code (RC).  Hence set countif to zero.
  3. After incrementing COUNTIF, update the hash object with the new countif value.

 

 

data want;
  set have;
  if _n_=1 then do;
    declare hash counts();
      counts.definekey('status');
      counts.definedata('countif');
      counts.definedone();
  end;
  rc=counts.find();
  if rc^=0 then countif=0;
  countif=countif+1;
  counts.replace();
  drop rc;
run;

 

 

xxx

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

3 REPLIES 3
ballardw
Super User

Since $A$2 and such don't have much actual meaning in the form of the example data provided (you don't provide a column or row indicator for anything) the question is incomplete.

 

Generally a "line by line" transform of a spreadsheet process will not work in SAS as most things are row oriented. The main exception is Proc IML to handle matrix data.

 

 

Astounding
PROC Star

Usually, SAS would use separate counters for each STATUS.  Matching to your example:

 

CountX   CountY

0                 1

0                 2

0                 3

1                 3

1                 4

2                 4

2                 5

 

You could construct COUNTIF at the same time, but it's much easier to talk about if you were to give a SAS example instead of an Excel example.

mkeintz
PROC Star

You want cumulative frequency, from record 1 through current record, of the current value of  status.  So let's assume you create dataset HAVE with the series of status values.

 

If you know in advance all the possible values of status, then this is short and simple.  It keeps an array of counts with one element per expected value of status.  Arrays with the _TEMPORARY_ description will have their values persist from record to record, but  will not be stored in the data set - a perfect place to keep running counts. The FIND function return the posiition in the first argument where the second argument is found.:

 

data have;
  input status :$1. @@;
datalines;
Y Y Y X Y X Y
run;

data want;
  array counts {2} _temporary_ (2*0);
  set have;
  index=find('XY',status);
  counts{index}+1;
  countif=counts{index};
  drop index;
run;

 

If you don't know in advance all the status values in have, then the thing to do is create a hash object, keyed on STATUS and storing the running countif value for each status, as below.

 

  1. The least intuitive thing about the hash object in this program is that it is declared only once overall (not once per observations).  That's why you see object COUNTS declared and defined in the "if _n_=1 then do" group.  "_N_=1" in this program is true only when the first observation is current.
  2. The FIND() method retrieves the current value of COUNTIF based on the status value.  If the status value is not already in the object, then FiND() fails, and yields a non-zero return code (RC).  Hence set countif to zero.
  3. After incrementing COUNTIF, update the hash object with the new countif value.

 

 

data want;
  set have;
  if _n_=1 then do;
    declare hash counts();
      counts.definekey('status');
      counts.definedata('countif');
      counts.definedone();
  end;
  rc=counts.find();
  if rc^=0 then countif=0;
  countif=countif+1;
  counts.replace();
  drop rc;
run;

 

 

xxx

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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
  • 3 replies
  • 711 views
  • 0 likes
  • 4 in conversation