Desktop productivity for business analysts and programmers

Calculating the observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Calculating the observations

I have a dataset with subject ,month and treatment.

I want to figure out how to calculate the subject with the following criteria:

The subjects have been in the treatment for the whole time during the year in every month. If the subject has been given a placebo in between then i need to account only those months that he receives drug after the break when he received the placebo.for a year for the months 1-12 .

usubjid   months  treatment

usubid1     01      drug

usubid1     02     placebo

usubjid 1   03      drug and then he is on drug continuously.

so i want in my dataset usubjid1 from month 3 till 12.

the dataset is sorted in this order usubjid/month


Accepted Solutions
Solution
‎07-12-2013 02:10 AM
Super Contributor
Posts: 297

Re: Calculating the observations

Is this more suitable?  I changed the data you provided slightly so that the results could be better displayed.

DATA HAVE;

LENGTH TREATMENTS $4;

INFILE DATALINES DELIMITER=',';

INPUT USUBJID $ MONTH $ TREATMENTS $;

DATALINES;

USUBJID1,01,DRUG

USUBJID1,02,DRUG

USUBJID1,04,DRUG

USUBJID2,05,DRUG

USUBJID2,06,DRUG

USUBJID1,10,DRUG

USUBJID1,11,DRUG

USUBJID1,12,DRUG

USUBJID2,11,DRUG

;

RUN;

PROC SORT DATA = HAVE /*(DROP = TREATMENTS)*/;

  BY USUBJID MONTH;

RUN;

PROC TRANSPOSE DATA = HAVE OUT=TRANSHAVE (DROP=_NAME_);

  BY USUBJID;

  ID MONTH;

  VAR TREATMENTS;

RUN;

DATA LASTCONSEC (DROP = '01'N '02'N '03'N '04'N '05'N '06'N '07'N '08'N '09'N '10'N '11'N '12'N);

  ATTRIB '01'N '02'N '03'N '04'N '05'N '06'N '07'N '08'N '09'N '10'N '11'N '12'N LENGTH = $4;

  SET TRANSHAVE;

 

  ARRAY TRANSVARS '01'N-'12'N;

  DO I = 1 TO 12;

   IF I = 1 THEN DO;

     IF TRANSVARS{I} = "DRUG" THEN STARTMTH = VNAME(TRANSVARS{I});

  IF TRANSVARS{I} = "DRUG" THEN ENDMTH = VNAME(TRANSVARS{I});

   END;

   ELSE DO;

      IF TRANSVARS{I-1} = "" AND TRANSVARS{I} ~= "" THEN STARTMTH = VNAME(TRANSVARS{I});

  IF TRANSVARS{I} = "DRUG" THEN ENDMTH = VNAME(TRANSVARS{I});

   END;

  END;

  DROP I;

RUN;

DATA WANT;

  MERGE LASTCONSEC (IN=A)

   HAVE (IN=B);

  BY USUBJID;

  IF A AND B

  AND STARTMTH <= MONTH <= ENDMTH;

  DROP STARTMTH ENDMTH;

RUN;

View solution in original post


All Replies
Super User
Posts: 12,148

Re: Calculating the observations

Do you have multiple years in your data? For example, would a subject have months in order 10, 11, 12, 01, ... 09 to represent one year of data?

Occasional Contributor
Posts: 7

Re: Calculating the observations

Thank you for getting back. It is one year. no multiple years.

Super Contributor
Posts: 297

Re: Calculating the observations

Hi MKPK,

Does this do what you are after?

DATA HAVE;

INFILE DATALINES DELIMITER=',';

INPUT USUBJID $ MONTH $ TREATMENTS $;

DATALINES; 

USUBJID1,01,DRUG

USUBJID1,02,PLACEBO

USUBJID1,03,DRUG

USUBJID1,04,DRUG

USUBJID1,05,DRUG

USUBJID1,06,DRUG

USUBJID1,07,DRUG

USUBJID1,08,DRUG

USUBJID1,09,DRUG

USUBJID1,10,DRUG

USUBJID1,11,DRUG

USUBJID1,12,DRUG

;

RUN;

PROC SORT DATA=HAVE;

BY USUBJID MONTH;

RUN;

DATA WANT;

  MERGE HAVE (IN=A WHERE=(PTREATMENTS = "PLACEBO") RENAME = (MONTH = PMONTH TREATMENTS = PTREATMENTS))

   HAVE (IN=B);

  BY USUBJID;

  IF A AND B AND MONTH > PMONTH;

  DROP PMONTH PTREATMENTS;

RUN;

Occasional Contributor
Posts: 7

Re: Calculating the observations

Posted in reply to Scott_Mitchell

Hi

Thank you for getting back to me.

My dataset has only treatment in it but there are gap in the months that the subject receives drug and i need only those subjects who have remained on drug 100 % after they have come back from the gap.

DATA HAVE;

INFILE DATALINES DELIMITER=',';

INPUT USUBJID $ MONTH $ TREATMENTS $;

DATALINES;

USUBJID1,01,DRUG

USUBJID1,02,drug

USUBJID1,04,DRUG

USUBJID2,05,DRUG

USUBJID2 06,DRUG

USUBJID1,10,DRUG

USUBJID1,11,DRUG

USUBJID1,12,DRUG

USUBJID2,10,DRUG

USUBJID2,11,DRUG

USUBJID2,12,DRUG

;

RUN;

From from the dataset above as you see all trt is same  but i want only from  the count of subjied from week 10-12 as well as subjid 2 from week 10-12 only.

Thankx a lot

Solution
‎07-12-2013 02:10 AM
Super Contributor
Posts: 297

Re: Calculating the observations

Is this more suitable?  I changed the data you provided slightly so that the results could be better displayed.

DATA HAVE;

LENGTH TREATMENTS $4;

INFILE DATALINES DELIMITER=',';

INPUT USUBJID $ MONTH $ TREATMENTS $;

DATALINES;

USUBJID1,01,DRUG

USUBJID1,02,DRUG

USUBJID1,04,DRUG

USUBJID2,05,DRUG

USUBJID2,06,DRUG

USUBJID1,10,DRUG

USUBJID1,11,DRUG

USUBJID1,12,DRUG

USUBJID2,11,DRUG

;

RUN;

PROC SORT DATA = HAVE /*(DROP = TREATMENTS)*/;

  BY USUBJID MONTH;

RUN;

PROC TRANSPOSE DATA = HAVE OUT=TRANSHAVE (DROP=_NAME_);

  BY USUBJID;

  ID MONTH;

  VAR TREATMENTS;

RUN;

DATA LASTCONSEC (DROP = '01'N '02'N '03'N '04'N '05'N '06'N '07'N '08'N '09'N '10'N '11'N '12'N);

  ATTRIB '01'N '02'N '03'N '04'N '05'N '06'N '07'N '08'N '09'N '10'N '11'N '12'N LENGTH = $4;

  SET TRANSHAVE;

 

  ARRAY TRANSVARS '01'N-'12'N;

  DO I = 1 TO 12;

   IF I = 1 THEN DO;

     IF TRANSVARS{I} = "DRUG" THEN STARTMTH = VNAME(TRANSVARS{I});

  IF TRANSVARS{I} = "DRUG" THEN ENDMTH = VNAME(TRANSVARS{I});

   END;

   ELSE DO;

      IF TRANSVARS{I-1} = "" AND TRANSVARS{I} ~= "" THEN STARTMTH = VNAME(TRANSVARS{I});

  IF TRANSVARS{I} = "DRUG" THEN ENDMTH = VNAME(TRANSVARS{I});

   END;

  END;

  DROP I;

RUN;

DATA WANT;

  MERGE LASTCONSEC (IN=A)

   HAVE (IN=B);

  BY USUBJID;

  IF A AND B

  AND STARTMTH <= MONTH <= ENDMTH;

  DROP STARTMTH ENDMTH;

RUN;

Occasional Contributor
Posts: 7

Re: Calculating the observations

Posted in reply to Scott_Mitchell

Thank you Very much.

This has helped me a lot.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 411 views
  • 3 likes
  • 3 in conversation