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
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.
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
Are you sure? Please post the exact code used and a print of the results.
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;
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.
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.