DATA Step, Macro, Functions and more

creating counter

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

creating counter

In Analytics, Data speaks better than words ! So please have a look at the following test data which I arranged using sort command.

NumberDate
123412-Sep- 15
123412-Jan-16
123412-Jan-16
123412-Jan-16
225412-Sep-15
225413-Sep-15
223513-Sep-15
251013-Sep-15
251013-Sep-15

 

I have sorted the data by using sort number, date and now I want to insert a new column called counter as below

 

NumberDateCounter
123412-Sep-151
123412-Jan-162
123412-Jan-162
123412-Jan-162
225412-Sep-151
225413-Sep-152
223513-Sep-151
251013-Sep-151
251013-Sep-151

 

This counter gives the number of dates for particular group of numbers. Example in the first group namely '1234' has two dates so counter is 1 for first date and 2 for second date irrespective of its number of occurences.

After adding this column, I also want to see the statistics of this new column called 'counter' like maximum value, average value etc., if there is some predefined procedure for the same, I will be grateful for knowledge sharing.

Thanks in advance ! 


Accepted Solutions
Solution
‎08-23-2016 10:48 PM
Super User
Posts: 9,691

Re: creating counter

data HAVE;
 input Number Date date7.;
cards; 
1234 12Sep15 
1234 12Jan16 
1234 12Jan16 
1234 12Jan16 
2254 12Sep15 
2254 13Sep15 
2265 13Sep15 
2510 13Sep15 
2510 13Sep15 
run;
data WANT;
  set HAVE; 
  by  NUMBER Date notsorted;
  if first.NUMBER then COUNT=0;
  count+first.Date;
run;

View solution in original post


All Replies
PROC Star
Posts: 1,567

Re: creating counter

Like this?

 

data HAVE;
 input Number Date date7.;
cards; 
1234 12Sep15 
1234 12Jan16 
1234 12Jan16 
1234 12Jan16 
2254 12Sep15 
2254 13Sep15 
2265 13Sep15 
2510 13Sep15 
2510 13Sep15 
run;
data WANT;
  set HAVE; by  NUMBER ;
  if first.NUMBER then COUNTER=0;
  if first.NUMBER | DATE ne lag(DATE) then COUNTER+1;
run;
proc print data=WANT noobs; 
run;
proc means data=WANT max mean;
  class NUMBER;
  var COUNTER;
run;
  

Number Date COUNTER
1234 20343 1
1234 20465 2
1234 20465 2
1234 20465 2
2254 20343 1
2254 20344 2
2265 20344 1
2510 20344 1
2510 20344 1
Analysis Variable : COUNTER
Number N Obs Maximum Mean
1234 4 2.0000000 1.7500000
2254 2 2.0000000 1.5000000
2265 1 1.0000000 1.0000000
2510 2 1.0000000 1.0000000

 

 

 

 

Trusted Advisor
Posts: 1,131

Re: creating counter

data want;
do until(first.date);
set have;
by number date;
retain count;
if first.number then count=1;
else count+1;
end;
do until(last.date);
set have;
by number date;
output;
end;
run;

Thanks,
Jag
Solution
‎08-23-2016 10:48 PM
Super User
Posts: 9,691

Re: creating counter

data HAVE;
 input Number Date date7.;
cards; 
1234 12Sep15 
1234 12Jan16 
1234 12Jan16 
1234 12Jan16 
2254 12Sep15 
2254 13Sep15 
2265 13Sep15 
2510 13Sep15 
2510 13Sep15 
run;
data WANT;
  set HAVE; 
  by  NUMBER Date notsorted;
  if first.NUMBER then COUNT=0;
  count+first.Date;
run;

Contributor
Posts: 71

Re: creating counter

@Ksharp why 'not sorted' is used here ? Is it because the file is already sorted ? 

Super User
Posts: 9,691

Re: creating counter

If your table has already been sorted , then remove that NOTSORTED .
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 373 views
  • 6 likes
  • 4 in conversation