how to assign a number to a date series

[ashish112]

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

[novinosrin]

data want;

set have;

number=_n_;

run;

or

data want;

set have;

number+1;

run;

[ashish112]

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

[novinosrin]

Hi @ashish112  Can you post a better sample of what you have and want. Make the sample in excel and paste here. 

[ashish112]

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.

[Reeza]

proc sort data=have;
by date;
run;

data want;
set have;
by date;
retain counter 0;
if first.date then counter+1;
run;

[novinosrin]

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;

[Reeza]

---

@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?

[ashish112]

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.

[Reeza · Accepted solution]

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]

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.

[Reeza]

---

@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.

[ashish112]

I first sort the data by date and create variable counter, but now I want
the file in format by item and then by date. So for that I have written
that code.

[Reeza]

You sort with PROC SORT or PROC SQL.

[ashish112]

Ok, got it,sometimes its confusing for the beginner. Thanks for your help.