Get count of rows

[Gil_]

I have a data step thst gives me count
Of row per my grouping

Here it is
Data dep5;
Set dep5;
Count +1;
By top;
If first.top then count =1;
Run;
It works but it starts with the earliest date as 1 and most current with 5 I would need it to be the opposite of that for example

Top. Date. Count. Need to look
1. 02/01/2018. 1. 3
2. 02/05/2018. 2. 2
3. 03/01/2018. 3. 1

[Jagadishkatam]

please try

data have ;
input Top Date: mmddyy10.  ;
format date date9.;
cards;
1 02/01/2018 
2 02/05/2018 
3 03/01/2018 
;

proc sort data=have ;
by descending date;
run;

data want;
set have;
by descending date ;
retain count;
if first.date then count+1;
run;

[SuryaKiran]

First you sort your data by descending and count the records by id and then sort back to ascending.

data have;                                                                                                                              
format date date9.;                                                                                                                     
infile datalines dlm=" ";                                                                                                               
input ID $ Date date9. ;                                                                                                                
datalines;                                                                                                                              
A 01MAR2018                                                                                                                             
A 02MAR2018                                                                                                                             
A 03MAR2018                                                                                                                             
B 01MAR2018                                                                                                                             
B 02MAR2018                                                                                                                             
B 03MAR2018                                                                                                                             
;                                                                                                                                       
run;                                                                                                                                    
                                                                                                                                        
proc sort data=have;                                                                                                                    
by id descending date;                                                                                                                  
run;                                                                                                                                    
                                                                                                                                        
data want;                                                                                                                              
set have;                                                                                                                               
by id;                                                                                                                                  
if first.id then count=1;                                                                                                               
else count+1;                                                                                                                           
run;                                                                                                                                    
proc sort data=want;                                                                                                                    
by id date;                                                                                                                             
proc print data=want;                                                                                                                   
run; 

[Ksharp]

data have ;
input Top Date: mmddyy10.  ;
format date date9.;
cards;
1 02/01/2018 
2 02/05/2018 
3 03/01/2018 
;
data want;
 set have nobs=nobs;
 want=nobs+1-_n_;
run;

[Gil_]

Thanks for response SuryaKiran 
I'm still getting the number sequence from the earliest being one and latest with 20 since that number can fluctuate

[SuryaKiran]

Proper sort order will solve your problem. Please provide with some actual sample data with few records.

[Gil_]

Hi Kshatp,
I use your code an the output was
Dep5. Date. Id
146070, 03/18/18. 1536
146069, 03/28/18. 2334

[Gil_]

Jagadishkatam ,
I tried your script I need to incorporate Id for grouping

Id Date. Count. Need to look
1. 02/01/2018. 1. 3
2. 02/05/2018. 1. 3
3. 03/01/2018. 1. 3
1. 02/03/2018. 2. 2
2. 02/07/2018. 2. 2
3. 03/04/2018. . 2. 2
1. 02/04/2018b. . 3. 1
2. 02/08/2018. 3. 1
3. 03/09/2018. 3. 1

[Gil_]

Hi Ksharp
I got it to work with your example ..How can refer dep5 instead datalines?

[Ksharp · Accepted solution]

data want;
 set dep5 nobs=nobs;
 want=nobs+1-_n_;
run;

[Gil_]

SuryaKiran 


How do I refer table dep5 it's in lib work in your code ?

[SuryaKiran]

proc sort data=dep5;                                                                                                                    
by top descending date;                                                                                                                  
run;                                                                                                                                    
                                                                                                                                        
data dep5;                                                                                                                              
set dep5;                                                                                                                               
by top;                                                                                                                                  
if first.top then count=1;                                                                                                               
else count+1;                                                                                                                           
run;                                                                                                                                    
proc sort data=dep5;                                                                                                                    
by top date;                                                                                                                             
proc print data=dep5;                                                                                                                   
run; 

[Gil_]

Thank for assistance the data is still giving me count from latest date to one and current date with higher number

Here is example of what I need
Id. Date. Count
Was. 01/03/2018. 5
Was. 01/04/2018. 4
Was. 01/05/2018. 3
Was. 01/06/2018. 2
Was. 01/07/2018. 1
Abc 01/03/2018. 5
Abc 01/04/2018. 4
Abc 01/05/2018. 3
Abc 01/03/2018. 2
Abc 01/02/2018. 1


When I use code you provided it does the opposite. Thank you for assistance

[Jagadishkatam]

could you please try this code, it is the same I provided earlier 

not sure if you tried earlier but if you could try it will help you 

proc sort data=have ;
by descending date;
run;

data want;
set have;
by descending date ;
retain count;
if first.date then count+1;
run;

proc sort data=want;
by id date;
run;