topic Get count of rows in SAS Procedures

Get count of rows

Gil_ — Mon, 19 Mar 2018 22:08:16 GMT

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

Re: Get count of rows

Jagadishkatam — Mon, 19 Mar 2018 23:12:59 GMT

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;

Re: Get count of rows

SuryaKiran — Mon, 19 Mar 2018 23:16:58 GMT

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;

Re: Get count of rows

Ksharp — Tue, 20 Mar 2018 12:33:45 GMT

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;

Re: Get count of rows

Gil_ — Tue, 20 Mar 2018 14:27:59 GMT

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

Re: Get count of rows

Gil_ — Tue, 20 Mar 2018 14:31:33 GMT

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

Re: Get count of rows

Gil_ — Tue, 20 Mar 2018 14:43:26 GMT

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

Re: Get count of rows

Gil_ — Tue, 20 Mar 2018 19:35:07 GMT

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

Re: Get count of rows

SuryaKiran — Tue, 20 Mar 2018 19:49:45 GMT

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

Re: Get count of rows

Gil_ — Tue, 20 Mar 2018 20:47:40 GMT

SuryaKiran

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

Re: Get count of rows

Ksharp — Wed, 21 Mar 2018 12:12:01 GMT

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

Re: Get count of rows

SuryaKiran — Wed, 21 Mar 2018 18:39:52 GMT

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;

Re: Get count of rows

Gil_ — Thu, 22 Mar 2018 15:10:49 GMT

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

Re: Get count of rows

Jagadishkatam — Sat, 24 Mar 2018 08:51:19 GMT

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;