CODEWITHSUNDEEP
sas
Sinbad Sailor
Sinbad Sailor

Reputation: 57

SAS: Create a frequency variable

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.

Upvotes: 1

Views: 1258

Answers (1)

Joe
Joe

Reputation: 63424

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).

Upvotes: 2

Related Questions