topic Re: how to assign a number to a date series in New SAS User

how to assign a number to a date series

ashish112 — Tue, 20 Nov 2018 15:44:42 GMT

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

Re: how to assign a number to a date series

novinosrin — Tue, 20 Nov 2018 15:46:18 GMT

data want;

set have;

number=_n_;

run;

data want;

set have;

number+1;

run;

Re: how to assign a number to a date series

PaigeMiller — Tue, 20 Nov 2018 15:46:43 GMT

data want;
    set have;
    number=_n_;
run;

Re: how to assign a number to a date series

r_behata — Tue, 20 Nov 2018 15:58:32 GMT

proc sql;
create table test
as select air,date,monotonic() as num
from sashelp.air
order by date;
quit;

Re: how to assign a number to a date series

RW9 — Tue, 20 Nov 2018 16:05:11 GMT

I would be very careful with the monotonic() function. It may not behave in the way you expect it to. In very basic scenarios it may be an option, however in most I would avoid using it at all. The datastep version (as @PaigeMiller posted) is simpler and will work as expected.

Re: how to assign a number to a date series

ashish112 — Tue, 20 Nov 2018 16:09:49 GMT

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

Re: how to assign a number to a date series

ashish112 — Tue, 20 Nov 2018 16:10:09 GMT

Re: how to assign a number to a date series

ashish112 — Tue, 20 Nov 2018 16:11:02 GMT

Thanks for quick reply, let me tell 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

Re: how to assign a number to a date series

Reeza — Tue, 20 Nov 2018 16:12:34 GMT

Use BY Groups then instead - in the future please state issues like this at the beginning.

Tutorial is here:

https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

@ashish112 wrote:
Thanks for quick reply, let me tell 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

Re: how to assign a number to a date series

Shmuel — Tue, 20 Nov 2018 16:40:24 GMT

If I understand correctly, you need first step to sort your data by date then use the proposed solution;

what kind of date is '########' ? In case of sort those dates will be either at start or at end depends on OS.

proc sort data=have; by date; run;
data want;
 set have;
     number = _N_;
run;

Re: how to assign a number to a date series

novinosrin — Tue, 20 Nov 2018 16:45:44 GMT

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

Re: how to assign a number to a date series

ashish112 — Tue, 20 Nov 2018 16:56:51 GMT

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.

Re: how to assign a number to a date series

Reeza — Tue, 20 Nov 2018 16:59:31 GMT

proc sort data=have;
by date;
run;

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

Re: how to assign a number to a date series

novinosrin — Tue, 20 Nov 2018 17:01:37 GMT

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;

Re: how to assign a number to a date series

Astounding — Tue, 20 Nov 2018 17:08:24 GMT

Looking at the example you posted, sorting your data is out of the question. Instead, use this variation:

data want;

set have;

by date notsorted;

if first.date then number + 1;

run;

Re: how to assign a number to a date series

Reeza — Tue, 20 Nov 2018 17:10:58 GMT

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

Re: how to assign a number to a date series

ashish112 — Tue, 20 Nov 2018 17:17:42 GMT

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.

Re: how to assign a number to a date series

Reeza — Tue, 20 Nov 2018 17:25:17 GMT

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.

Re: how to assign a number to a date series

ashish112 — Tue, 20 Nov 2018 17:59:10 GMT

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.

Re: how to assign a number to a date series

Reeza — Tue, 20 Nov 2018 18:02:47 GMT

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