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: 11,144

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

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

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
  • 397 views
  • 3 likes
  • 3 in conversation