topic Re: sas query in SAS Procedures

sas query

rakeshvvv — Wed, 03 Jun 2015 19:21:20 GMT

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.

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

Re: sas query

ballardw — Wed, 03 Jun 2015 19:28:13 GMT

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.

Re: sas query

rakeshvvv — Wed, 03 Jun 2015 19:47:02 GMT

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

Re: sas query

AskoLötjönen — Thu, 04 Jun 2015 06:11:38 GMT

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;