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:
Status | Countif | Formula |
Y | 1 | =COUNTIF($A$2:$A2,A2) |
Y | 2 | =COUNTIF($A$2:$A3,A3) |
Y | 3 | =COUNTIF($A$2:$A4,A4) |
X | 1 | =COUNTIF($A$2:$A5,A5) |
Y | 4 | =COUNTIF($A$2:$A6,A6) |
X | 2 | =COUNTIF($A$2:$A7,A7) |
Y | 5 | =COUNTIF($A$2:$A8,A8) |
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.
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
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.
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.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.