Re: Create sequential ID by multiple variable without considering date

gabras · Posted 10-06-2018 09:24 AM

Hi everybody,

i need to enumerate the records based on three variables. The dataset must be orderd in ascending order by date.

I tried the following,

data want;
set have;
ord + 1;
by ID CREATED PROD SUBPROD ;
if first.ID or first.PROD or first.SUBPROD
then ord = 1;

run;

but it doesn't work as you can se in the table below:

ID	CREATED	PROD	SUBPROD	ORD
1	01-ott-18	A	A	1
1	02-ott-18	A	A	1
1	02-ott-18	A	A	2
1	02-ott-18	A	A	3
1	05-ott-18	B	B	1
2	02-ott-18	A	A	1
3	03-ott-18	B	B	1
3	04-ott-18	B	B	1
3	05-ott-18	B	B	1
4	01-ott-18	C	C	1
4	02-ott-18	C	C	1
4	02-ott-18	C	C	2
4	02-ott-18	D	D	1
4	05-ott-18	D	D	1
4	06-ott-18	D	D	1

The data set i want has to be like this:

ID	CREATED	PROD	SUBPROD	ORD
1	01-ott-18	A	A	1
1	02-ott-18	A	A	2
1	02-ott-18	A	A	3
1	02-ott-18	A	A	4
1	05-ott-18	B	B	1
2	02-ott-18	A	A	1
3	03-ott-18	B	B	1
3	04-ott-18	B	B	2
3	05-ott-18	B	B	3
4	01-ott-18	C	C	1
4	02-ott-18	C	C	2
4	02-ott-18	C	C	3
4	02-ott-18	D	D	1
4	05-ott-18	D	D	2
4	06-ott-18	D	D	3

Thank you

PaigeMiller · Posted 10-06-2018 09:28 AM

Without considering date:

proc sort data=have;
    by id prod subprod;
run;

data want;
    set have;
    by id prod subprod;
    if first.subprod then ord=0;
    ord+1;
run;

--
Paige Miller

gabras · Posted 10-06-2018 09:36 AM

Is the table have sorted in ascending order by date?

PaigeMiller · Posted 10-06-2018 09:38 AM

So you can add date into the PROC SORT at the end of the BY statement.

--
Paige Miller

gabras · Posted 10-06-2018 09:56 AM

Ok, i'll try .

I have to ask yo one question.

Why you wrote just first.subprod in the if statement without writing the three variables?

Thank you

novinosrin · Posted 10-06-2018 10:32 AM

If your data is already sorted by date,

data have;
input (ID CREATED PROD SUBPROD) ($);
cards;
1 01-ott-18 A A 1
1 02-ott-18 A A 1
1 02-ott-18 A A 2
1 02-ott-18 A A 3
1 05-ott-18 B B 1
2 02-ott-18 A A 1
3 03-ott-18 B B 1
3 04-ott-18 B B 1
3 05-ott-18 B B 1
4 01-ott-18 C C 1
4 02-ott-18 C C 1
4 02-ott-18 C C 2
4 02-ott-18 D D 1
4 05-ott-18 D D 1
4 06-ott-18 D D 1
;

data want;
set have;
by ID PROD SUBPROD ;
if first.subprod then ord=1;
else ord+1;
run;

mkeintz · Posted 10-06-2018 10:58 AM

Just to be cute, here is a one-step solution, possibly handy with a large (but sorted) raw data file:

data have;
input (ID CREATED PROD SUBPROD) ($); 
  ord+1;
  if     catx(' ',id,prod,subprod)
   ^=lag(catx(' ',id,prod,subprod)) then ord=1;
cards;
1 01-ott-18 A A 1
1 02-ott-18 A A 1
1 02-ott-18 A A 2
1 02-ott-18 A A 3
1 05-ott-18 B B 1
2 02-ott-18 A A 1
3 03-ott-18 B B 1
3 04-ott-18 B B 1
3 05-ott-18 B B 1
4 01-ott-18 C C 1
4 02-ott-18 C C 1
4 02-ott-18 C C 2
4 02-ott-18 D D 1
4 05-ott-18 D D 1
4 06-ott-18 D D 1
;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

PaigeMiller · Posted 10-06-2018 11:38 AM

@gabras wrote:

Ok, i'll try .

I have to ask yo one question.

Why you wrote just first.subprod in the if statement without writing the three variables?

Thank you

It is not necessary (and wrong) to include all three variables. You want the ORD variable to reset when SUBPROD changed, not when all three change.

--
Paige Miller

gabras · Posted 10-06-2018 12:06 PM

I was too superficial.

I can also have the following situation:

ID	CREATED	PROD	SUBPROD	ITEM
1	01-ott-18	A	B	D
1	02-ott-18	A	B	D
1	02-ott-18	A	B	D
1	02-ott-18	A	B	D
1	05-ott-18	A	A	E

and i want this

ID	CREATED	PROD	SUBPROD	ITEM	ORD
1	01-ott-18	A	B	D	1
1	02-ott-18	A	B	D	2
1	02-ott-18	A	B	D	3
1	02-ott-18	A	B	D	4
1	05-ott-18	A	A	E	1

VDD · Posted 10-06-2018 12:24 PM

did you try @mkeintz code. That logic should work in your case.

data have;
input (ID CREATED PROD SUBPROD) ($); 
  ord+1;
  if     catx(' ',id,prod,subprod)
   ^=lag(catx(' ',id,prod,subprod)) then ord=1;
cards;
1 01-ott-18 A A 1
1 02-ott-18 A A 1
1 02-ott-18 A A 2
1 02-ott-18 A A 3
1 05-ott-18 B B 1
2 02-ott-18 A A 1
3 03-ott-18 B B 1
3 04-ott-18 B B 1
3 05-ott-18 B B 1
4 01-ott-18 C C 1
4 02-ott-18 C C 1
4 02-ott-18 C C 2
4 02-ott-18 D D 1
4 05-ott-18 D D 1
4 06-ott-18 D D 1
;

PaigeMiller · Posted 10-06-2018 02:00 PM

@gabras wrote:

I was too superficial.

I can also have the following situation:

ID CREATED PROD SUBPROD ITEM

1 01-ott-18 A B D

1 02-ott-18 A B D

1 02-ott-18 A B D

1 02-ott-18 A B D

1 05-ott-18 A A E

and i want this

ID CREATED PROD SUBPROD ITEM ORD

1 01-ott-18 A B D 1

1 02-ott-18 A B D 2

1 02-ott-18 A B D 3

1 02-ott-18 A B D 4

1 05-ott-18 A A E 1

Surely you can modify the code I provided to handle this situation.

--
Paige Miller

gabras · Posted 10-07-2018 07:23 AM

Could you help me please

mkeintz · Posted 10-07-2018 08:08 AM

You have asked for help.

Help was provided.

If you need more help, please show the code you tried, and show the result of that code, and describe what part of the result is in error.

Help us help you.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

PaigeMiller · Posted 10-07-2018 11:54 AM

@gabras wrote:
Could you help me please

Earlier, I stated the explanation

It is not necessary (and wrong) to include all three variables. You want the ORD variable to reset when SUBPROD changed, not when all three change.

so apply this explanation to your new situation involving variable ITEM.

--
Paige Miller

SAS Innovate 2025: Save the Date

SAS Training: Just a Click Away