topic Re: How to assign seq values by sort order in SAS Programming

How to assign seq values by sort order

knveraraju91 — Wed, 24 Aug 2016 19:17:07 GMT

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

2 2012-01-02 FISH OIL

2 2012-01-02 MENS MULTIVITAMIN

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

2 2012-01-02 FISH OIL 1

2 2012-01-02 MENS MULTIVITAMIN 2

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

Re: How to assign seq values by sort order

ballardw — Wed, 24 Aug 2016 19:39:39 GMT

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.

Re: How to assign seq values by sort order

Reeza — Wed, 24 Aug 2016 19:41:02 GMT

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;

Re: How to assign seq values by sort order

knveraraju91 — Wed, 24 Aug 2016 23:43:46 GMT

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

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-03-02 MERINA (LEVONORGESTREL)

Output need;

id date trt SEQ

1 2010-09-01 DEPO PROVERA 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-03-02 MERINA (LEVONORGESTREL) 2

Re: How to assign seq values by sort order

Reeza — Thu, 25 Aug 2016 00:25:27 GMT

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

Re: How to assign seq values by sort order

knveraraju91 — Thu, 25 Aug 2016 00:49:44 GMT

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;

Re: How to assign seq values by sort order

Ksharp — Thu, 25 Aug 2016 02:21:16 GMT

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;

Re: How to assign seq values by sort order

Reeza — Thu, 25 Aug 2016 05:36:45 GMT

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.

Re: How to assign seq values by sort order

Reeza — Thu, 25 Aug 2016 05:38:59 GMT

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.