how to add 1 to 7 sequence in entire dataset and identify 2 days on and 1 day off drug records ?

[kk26]

Hi 

I need to create variable ord which count 1 to 7 and then start again from 1 up to the last record of group usubjid,astdt,aendt in below example.

In addition to that how can i identify records "every third day" and 2 days on drug one day off ?  

Create table wk3tim1(usubjid varchar(), exdosfrq varchar(), astdt float, aendt float, startdate float);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-04-30);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-01);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-02);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-03);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-04);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-05);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-06);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-07);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-08);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-09);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-10);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-11);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-12);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-13);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-14);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-15);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-16);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-17);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-18);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-19);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-20);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-21);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-22);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-23);
Insert into wk3tim1(usubjid, exdosfrq, astdt, aendt, startdate) Values('255-083-002', '3 TIMES PER WEEK', 2019-04-30, 2019-07-20, 2019-05-24);

[ChrisNZ · Accepted solution]

This is not valid SAS code to create a SAS data set.

Please always test the code you post, by copying from the web page and pasting in SAS and running it.

This should do what you need:

data WANT;
  set HAVE;
  by USUBJID ;
  if first.USUBJID then DAYNO=0;
  if DAYNO=7 then DAYNO=1; else DAYNO+1;
  if mod(DAYNO,3)=1 then THIRDDAY=1;
run;