If I have a sorted table like this:
DATA myfruit;
INPUT fruit $40.;
DATALINES;
APPLE
APPLE
APPLE
PEACH
PEACH
;
RUN;
I would like to add the "Count" column like this:
Fruit | Count |
---|---|
APPLE | 1 |
APPLE | 2 |
APPLE | 3 |
PEACH | 1 |
PEACH | 2 |
I know that I have to do something like this, I can't get it quite right.
data myfruit;
set myfruit;
by fruit;
Count = _N_;
run;
Any help would be greatly appreciated.
Thanks!
Adam
I worked it out, so am posting the solution.
data myfruit;
set myfruit;
by fruit;
retain count;
if first.fruit then count = 1;
else count + 1;
run;
hi ...
data myfruit;
set myfruit;
by fruit;
count + (-first.fruit * count) + 1;
run;
ps ... http://www.sascommunity.org/wiki/Tips:Between_and_Within_Group_Counters
Assuming your dataset has already sorted before.
DATA myfruit; INPUT fruit $40.; DATALINES; APPLE APPLE APPLE PEACH PEACH ; RUN; data myfruit; set myfruit; if fruit ne lag(fruit) then count=0; Count+1; run;
Ksharp
Can also use DOW's counter:
data want;
do count=1 by 1 until (last.fruit);
set myfruit; by fruit; output;
end;
run;
Haikuo
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.
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.