sas query

[rakeshvvv]

Hi,

I have dataset with two variables ID and Visits. We expect the below following visits to be present for every ID. I would like to write a query to output the following scenario.

1. Every ID is expected to visit in incremental order of +1 from cycle1 to cycle n

Example:  if  ID visited upto CYCLE 9 but has skipped Cycle4,5,. Then it is discrepancy and we should output  Cycle4,5 as missing visit. The output should look like this.

ID                        TERM

001 missing: CYCLE 4, DAY 1:CYCLE 4, DAY 8:CYCLE 4, DAY 15 ; CYCLE 5, DAY 1:CYCLE 5, DAY 8:CYCLE 5, DAY 15

CYCLE 1, DAY 1

CYCLE 1, DAY 2

CYCLE 1, DAY 3

CYCLE 1, DAY 8

CYCLE 1, DAY 15

CYCLE 2, DAY 1

CYCLE 2, DAY 8

CYCLE 2, DAY 15

CYCLE 3, DAY 1

CYCLE 3, DAY 8

CYCLE 3, DAY 15

CYCLE 4, DAY 1

CYCLE 4, DAY 8

CYCLE 4, DAY 15

CYCLE 5, DAY 1

CYCLE 5, DAY 8

CYCLE 5, DAY 15

CYCLE 6, DAY 1

CYCLE 6, DAY 8

CYCLE 6, DAY 15

Can someone help me on this.....

Thanks

[ballardw]

Are the actual values in your Cycle variable "CYCLE 1" "CYCLE 2" etc or 1 2 3? I ask as one is easier to process.

[rakeshvvv]

As i have given in the sample....visits are present in below pattern...i know it will be trickier one but any inputs will be great help to me.....Thanks

CYCLE 1, DAY 1

CYCLE 1, DAY 2

CYCLE 1, DAY 3

CYCLE 1, DAY 8

CYCLE 1, DAY 15

CYCLE 2, DAY 1

CYCLE 2, DAY 8

CYCLE 2, DAY 15

CYCLE 3, DAY 1

CYCLE 3, DAY 8

CYCLE 3, DAY 15

CYCLE 4, DAY 1

CYCLE 4, DAY 8

CYCLE 4, DAY 15

CYCLE 5, DAY 1

CYCLE 5, DAY 8

CYCLE 5, DAY 15

CYCLE 6, DAY 1

CYCLE 6, DAY 8

CYCLE 6, DAY 15

[AskoLötjönen]

First I created a separate table for possible visits and I created SubjVisits table as your source table. Then I selected all subjects from that table and then I made a cartesian product to make table with all possible visits for all subjects. Then using proc sql except-clause I removed all existing visits.

data visits ;

input visit $ 1-20 ;

cards ;

CYCLE 1, DAY 1

CYCLE 1, DAY 2

CYCLE 1, DAY 3

CYCLE 1, DAY 8

CYCLE 1, DAY 15

CYCLE 2, DAY 1

CYCLE 2, DAY 8

CYCLE 2, DAY 15

CYCLE 3, DAY 1

CYCLE 3, DAY 8

CYCLE 3, DAY 15

CYCLE 4, DAY 1

CYCLE 4, DAY 8

CYCLE 4, DAY 15

CYCLE 5, DAY 1

CYCLE 5, DAY 8

CYCLE 5, DAY 15

CYCLE 6, DAY 1

CYCLE 6, DAY 8

CYCLE 6, DAY 15

;

run;

/* simlulated "existing" data */

data subjvisits;

input Subj $ 1-4 visit $ 6-25 ;

cards;

0001 CYCLE 1, DAY 1

0001 CYCLE 1, DAY 2

0001 CYCLE 1, DAY 3

0001 CYCLE 1, DAY 8

0001 CYCLE 1, DAY 15

0001 CYCLE 2, DAY 1

0001 CYCLE 2, DAY 8

0001 CYCLE 2, DAY 15

0001 CYCLE 3, DAY 1

0001 CYCLE 3, DAY 8

0001 CYCLE 3, DAY 15

0001 CYCLE 4, DAY 1

0001 CYCLE 4, DAY 8

0001 CYCLE 4, DAY 15

0001 CYCLE 5, DAY 1

0001 CYCLE 5, DAY 8

0001 CYCLE 5, DAY 15

0001 CYCLE 6, DAY 1

0001 CYCLE 6, DAY 8

0001 CYCLE 6, DAY 15

0002 CYCLE 1, DAY 1

0002 CYCLE 1, DAY 2

0002 CYCLE 1, DAY 3

0002 CYCLE 1, DAY 8

0002 CYCLE 1, DAY 15

0002 CYCLE 2, DAY 1

0002 CYCLE 2, DAY 8

0002 CYCLE 2, DAY 15

0002 CYCLE 4, DAY 1

0002 CYCLE 4, DAY 8

0002 CYCLE 4, DAY 15

0002 CYCLE 5, DAY 1

0002 CYCLE 5, DAY 8

0002 CYCLE 5, DAY 15

0002 CYCLE 6, DAY 1

0002 CYCLE 6, DAY 8

0002 CYCLE 6, DAY 15

0003 CYCLE 1, DAY 1

0003 CYCLE 1, DAY 2

0003 CYCLE 1, DAY 3

0003 CYCLE 1, DAY 8

0003 CYCLE 1, DAY 15

0003 CYCLE 2, DAY 1

0003 CYCLE 2, DAY 8

0003 CYCLE 2, DAY 15

0003 CYCLE 3, DAY 1

0003 CYCLE 3, DAY 8

0003 CYCLE 3, DAY 15

0003 CYCLE 5, DAY 1

0003 CYCLE 5, DAY 8

0003 CYCLE 5, DAY 15

0003 CYCLE 6, DAY 1

0003 CYCLE 6, DAY 8

0003 CYCLE 6, DAY 15

;

run;

proc sql;

create table subjects as

select distinct

       subj

from subjvisits;

quit;

proc sql;

  create table missing as

  select subj,

         visit

  from   subjects,

         visits

  except

  select subj,

         visit

  from   subjVisits;

quit;