Hi I want to assign a number in ascending order to a date column.
for example I have one column date as
date |
09/13/2015 |
09/20/2015 |
09/27/2015 |
######## |
######## |
10/18/2015 |
10/25/2015 |
######## |
######## |
11/15/2015 |
11/22/2015 |
11/29/2015 |
######## |
12/13/2015 |
12/20/2015 |
12/27/2015 |
######## |
######## |
01/17/2016 |
01/24/2016 |
01/31/2016 |
######## |
02/14/2016 |
02/21/2016 |
02/28/2016 |
######## |
03/13/2016 |
03/20/2016 |
03/27/2016 |
######## |
######## |
04/17/2016 |
04/24/2016 |
######## |
######## |
05/15/2016 |
05/22/2016 |
05/29/2016 |
######## |
######## |
06/19/2016 |
06/26/2016 |
######## |
######## |
07/17/2016 |
07/24/2016 |
07/31/2016 |
######## |
08/14/2016 |
08/21/2016 |
08/28/2016 |
######## |
and I want to assign a number to date (starts from 1 ) like below;
date | Number |
09/13/2015 | 1 |
09/20/2015 | 2 |
09/27/2015 | 3 |
######## | 4 |
######## | 5 |
10/18/2015 | 6 |
10/25/2015 | 7 |
######## | 8 |
######## | 9 |
11/15/2015 | 10 |
11/22/2015 | 11 |
11/29/2015 | 12 |
######## | 13 |
12/13/2015 | 14 |
12/20/2015 | 15 |
12/27/2015 | 16 |
######## | 17 |
######## | 18 |
01/17/2016 | 19 |
01/24/2016 | 20 |
01/31/2016 | 21 |
######## | 22 |
02/14/2016 | 23 |
02/21/2016 | 24 |
02/28/2016 | 25 |
######## | 26 |
03/13/2016 | 27 |
03/20/2016 | 28 |
03/27/2016 | 29 |
######## | 30 |
######## | 31 |
04/17/2016 | 32 |
04/24/2016 | 33 |
######## | 34 |
######## | 35 |
05/15/2016 | 36 |
05/22/2016 | 37 |
05/29/2016 | 38 |
######## | 39 |
######## | 40 |
06/19/2016 | 41 |
06/26/2016 | 42 |
######## | 43 |
######## | 44 |
07/17/2016 | 45 |
07/24/2016 | 46 |
07/31/2016 | 47 |
######## | 48 |
08/14/2016 | 49 |
08/21/2016 | 50 |
08/28/2016 | 51 |
######## | 52 |
That's more work than it's worth. You can just sort and use the approach I suggested earlier:
proc sort data=have;
by date;
run;
data want;
set have;
by date;
retain counter 0;
if first.date then counter+1;
run;
@ashish112 wrote:
An item does not have same date more than one time, and yes say a date Jan 1 2008 will have only one number across all items. simple way if we remove duplicate dates than only unique date will remains and we can assign a number counter by sorting date.
data want;
set have;
number=_n_;
run;
or
data want;
set have;
number+1;
run;
Hi @ashish112 Can you post a better sample of what you have and want. Make the sample in excel and paste here.
Hi , thanks for reply, I have attached sample file here. I want Period column as in file. For same date the period number also be same. so suppose I have 104 unique date the max number is 104. I hope it is clear now.
Ok it seems just a sequence for each item and nothing to do with date
data want;
set have;
by item;
if first.item then period=1;
else period+1;
run;
@ashish112 wrote:
Thanks for quick reply, let me t iell you my issue in detail. I have a date column where date is not in order, so in the first occurrence of date I want 1, in second occurrence I want 2, there may be cases when the dates are same, so for that dates I want same number. Hope it is clear
You have multiple items AND multiple dates. Can an item have the same date more than once? Are you saying a date, say Jan1 2018 needs to be the same across the different items, or it's just a counter of dates for reach Item?
An item does not have same date more than one time, and yes say a date Jan 1 2008 will have only one number across all items. simple way if we remove duplicate dates than only unique date will remains and we can assign a number counter by sorting date.
That's more work than it's worth. You can just sort and use the approach I suggested earlier:
proc sort data=have;
by date;
run;
data want;
set have;
by date;
retain counter 0;
if first.date then counter+1;
run;
@ashish112 wrote:
An item does not have same date more than one time, and yes say a date Jan 1 2008 will have only one number across all items. simple way if we remove duplicate dates than only unique date will remains and we can assign a number counter by sorting date.
@ashish112 wrote:
Yes and after that I have to sort again if I want it by Item and date.
Is my code correct?
Data want;
Set have;
By item date;
Run;
Sorry I am learning sas so it is not easy for me right now.
Correct for what, you didn't say what you're actually trying to do in that step. If it's sort, then no, it is not correct. You use PROC SORT to sort, similar to what I did in the answer above. Change the BY statement to have it sorted the way you want.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.