BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gil_
Quartz | Level 8
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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data want;
 set dep5 nobs=nobs;
 want=nobs+1-_n_;
run;

View solution in original post

13 REPLIES 13
Jagadishkatam
Amethyst | Level 16

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;

 

Thanks,
Jag
SuryaKiran
Meteorite | Level 14

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; 
Thanks,
Suryakiran
Ksharp
Super User
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_
Quartz | Level 8
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
Meteorite | Level 14

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

Thanks,
Suryakiran
Gil_
Quartz | Level 8
Hi Kshatp,
I use your code an the output was
Dep5. Date. Id
146070, 03/18/18. 1536
146069, 03/28/18. 2334
Gil_
Quartz | Level 8

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_
Quartz | Level 8
Hi Ksharp
I got it to work with your example ..How can refer dep5 instead datalines?
Ksharp
Super User
data want;
 set dep5 nobs=nobs;
 want=nobs+1-_n_;
run;
Gil_
Quartz | Level 8
SuryaKiran 


How do I refer table dep5 it's in lib work in your code ?
SuryaKiran
Meteorite | Level 14
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; 
Thanks,
Suryakiran
Gil_
Quartz | Level 8
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
Amethyst | Level 16

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;
Thanks,
Jag

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 13 replies
  • 2996 views
  • 3 likes
  • 4 in conversation