SAS: Create a frequency variable

Question

I need to create a frequency in variable, not a proc freq output but an actual variable within the dataset. I used the below code create a running count by ID.

 data work.frequencycounts;
 set work.dataset;
 count+1;
 by id;
 if first.id then count=1;
 run;

However, the variable I want is not a running count, it is the final count. I tried to add

 frequency=last.count;

to the end of the data step but that did not work.

Joe Joe · Accepted Answer · 2014-09-03T19:22:21

Each row in SAS is processed separately, most of the time. You can't directly take the proceeds of a pass through the dataset and place it on each row.

Fortunately, there are about 100 ways to accomplish this.

Here's the one most similar to your method.

data work.frequencycounts;
 do _n_ =1  by 1 until (last.id);
   set work.dataset;
   by id;
   if first.id then count=0;
   count+1;
 end;
 frequency=count;
 do _n_ = 1 by 1 until (last.id);
   set work.dataset;
   by id;
   output;
 end;
run;

This fairly straightforwardly runs through once, calculates the count, then places it on the dataset in the second pass. This is a double DoW loop.

Another option is to do the PROC FREQ and merge it on - pretty easy really. A third option is to use PROC SQL to calculate the count in a SQL step and merge it on (which SAS will do automatically for you).

SAS: Create a frequency variable

1 Answers