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.
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
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.
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
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.