BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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

 

8 REPLIES 8
ballardw
Super User

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.

knveraraju91
Barite | Level 11

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

 

Reeza
Super User

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

knveraraju91
Barite | Level 11

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;

 

 

 

Reeza
Super User

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. 

Reeza
Super User

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. 

Reeza
Super User

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;

 

 

 

 

 

Ksharp
Super User
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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2075 views
  • 3 likes
  • 4 in conversation