DATA Step, Macro, Functions and more

How to assign seq values by sort order

Reply
Super Contributor
Posts: 272

How to assign seq values by sort order

Dear,

 

I am having little trouble understanding the first.variable by sort order. I need to create SEQ variable. With my code i am not getting the out put i need. Please help.

 

data 

 

id               date                     trt             

1              2010-09-01           DEPO PROVERA

1              2010-09-01           DEPO PROVERA

1              2010-09-01           DEPO PROVERA

2              2012-01-02           FISH OIL

2              2012-01-02           FISH OIL

2              2012-01-02           MENS MULTIVITAMIN

3              2012-02-02           FISH OIL

3             2012-02-02           FISH OIL

3              2012-03-02           MERINA (LEVONORGESTREL)

 

 

Output need;

id               date                     trt                                                              SEQ

1              2010-09-01           DEPO PROVERA                                          1

1              2010-09-01           DEPO PROVERA                                          1

1              2010-09-01           DEPO PROVERA                                           1

2              2012-01-02           FISH OIL                                                        1

2              2012-01-02           FISH OIL                                                         1

2              2012-01-02           MENS MULTIVITAMIN                                    2

3              2012-02-02           FISH OIL                                                         1

3             2012-02-02           FISH OIL                                                          1

3              2012-03-02           MERINA (LEVONORGESTREL)                       2

 

MY CODE

proc sort data=ONE out=TWO;
by ID DATE TRT;
run;
data THREE;
set TWO;
by ID DATE TRT;

RETAIN CMSEQ;

if FIRST.CMTRT=1 THEN CMSEQ=1;
ELSE CMSEQ+1;
RUN;

 

OUTPUT BY THIS CODE;

Output need;

id               date                     trt                                                              SEQ

1              2010-09-01           DEPO PROVERA                                          1

1              2010-09-01           DEPO PROVERA                                          2

1              2010-09-01           DEPO PROVERA                                           3

2              2012-01-02           FISH OIL                                                        1

2              2012-01-02           FISH OIL                                                         2

2              2012-01-02           MENS MULTIVITAMIN                                    3

3              2012-02-02           FISH OIL                                                         1

3             2012-02-02           FISH OIL                                                          2

3              2012-03-02           MERINA (LEVONORGESTREL)                       3

 

Super User
Posts: 11,343

Re: How to assign seq values by sort order

Posted in reply to knveraraju91

Possibly this:

data THREE;
   set TWO;
   by ID DATE TRT;
   RETAIN CMSEQ;
   if FIRST.date=1 THEN CMSEQ=1;
   ELSE if first.cmtrt then CMSEQ+1;
RUN;

If you want to restart at the first DATE then use First.DATE

 

Your example does not show any ID with multiple dates so it is possible that if you want to restart at ID you want to reset CMSEQ=1 at First.Id. At which point your logic gets to be a bit different for the cmtrt. If so you will need to explain with some words what you are attempting as it may be a more complex issue or require a different sort order.

Super Contributor
Posts: 272

Re: How to assign seq values by sort order

Thank you for the help.

Your code gave me the output i need except at one place. For OBS 6th, I am getting SEQ= '1' instead of  SEQ='2' with the code. Please help

 

data 

 

id               date                     trt             

1              2010-09-01           DEPO PROVERA

1              2010-09-01           DEPO PROVERA

1              2010-09-01           DEPO PROVERA

2              2012-01-02           FISH OIL

2              2012-01-02           FISH OIL

2              2012-02-02           FISH OIL

2              2012-01-02           MENS MULTIVITAMIN

3              2012-02-02           FISH OIL

3             2012-02-02           FISH OIL

3              2012-03-02           MERINA (LEVONORGESTREL)

 

 

Output need;

id               date                     trt                                                              SEQ

1              2010-09-01           DEPO PROVERA                                          1

1              2010-09-01           DEPO PROVERA                                          1

1              2010-09-01           DEPO PROVERA                                           1

2              2012-01-02           FISH OIL                                                        1

2              2012-01-02           FISH OIL                                                         1

2              2012-02-02           FISH OIL                                                         2

2              2012-01-02           MENS MULTIVITAMIN                                    2

3              2012-02-02           FISH OIL                                                         1

3             2012-02-02           FISH OIL                                                          1

3              2012-03-02           MERINA (LEVONORGESTREL)                       2

 

Super User
Posts: 19,815

Re: How to assign seq values by sort order

Posted in reply to knveraraju91

Are you sure? Please post the exact code used and a print of the results. 

Super Contributor
Posts: 272

Re: How to assign seq values by sort order

I am attaching the original data document. Please look at OBS 46,47 with different dates and have SEQ number same. All OBS have same ID number

 

Code;

 

data three;

set two;

by  ID date TRT;
RETAIN SEQ;
if FIRST.date=1 THEN SEQ=1;
else IF FIRST.TRT=1 THEN SEQ+1;

run;

 

 

 

Super User
Posts: 19,815

Re: How to assign seq values by sort order

Posted in reply to knveraraju91

Well yeah, that's what you've asked for. Each date starts at 1, based on your rules this is correct. 

What would you expect to see? 

You also have a data quality issue. Is your date field an actual SAS date or a character field? 

Your dates shouldn't be showing different types. 

Super User
Posts: 19,815

Re: How to assign seq values by sort order

It looks like you want the reverse of the by groups, TRT before date. Change the order in the by group and the corresponding if statements. 

 

Please read into into how by groups and first. Works so you understand the logic and can modify it to your requirements in the future. 

Super User
Posts: 19,815

Re: How to assign seq values by sort order

Posted in reply to knveraraju91

FIRST. can only be applied to variables in your BY statement.

 

data THREE;
set TWO;
by ID DATE TRT;

RETAIN CMSEQ;

if FIRST.CMTRT=1 THEN CMSEQ=1; <<- This should be causing an error that you see in your log. Always read your log and include in your post if something isn't working as expected.
ELSE CMSEQ+1;
RUN;

 

 

data THREE;
set TWO;
by ID DATE TRT;
RETAIN CMSEQ;

if first.date then cmseq=0; *set to 0 if first date;
if first.trt then cmseq+1; *increment at each new treatment per date;

RUN;

 

 

 

 

 

Super User
Posts: 10,028

Re: How to assign seq values by sort order

Posted in reply to knveraraju91
data have;
input id               date   : $40.                  trt   & $40.;
cards;          
1              2010-09-01           DEPO PROVERA
1              2010-09-01           DEPO PROVERA
1              2010-09-01           DEPO PROVERA
2              2012-01-02           FISH OIL
2              2012-01-02           FISH OIL
2              2012-01-02           MENS MULTIVITAMIN
3              2012-02-02           FISH OIL
3             2012-02-02           FISH OIL
3              2012-03-02           MERINA (LEVONORGESTREL)
;
run;

data want;
 set have;
 by id trt;
 if first.id then seq=0;
 seq+first.trt;
run;
Ask a Question
Discussion stats
  • 8 replies
  • 484 views
  • 3 likes
  • 4 in conversation