BookmarkSubscribeRSS Feed
rakeshvvv
Quartz | Level 8

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

3 REPLIES 3
ballardw
Super User

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
Quartz | Level 8

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
Quartz | Level 8

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;

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 777 views
  • 0 likes
  • 3 in conversation