topic Re: Find last Cycle for subject and check whether all cycles before that are present or not in SAS Programming

Find last Cycle for subject and check whether all cycles before that are present or not

PK06 — Mon, 25 Oct 2021 07:50:46 GMT

Eg.
Required cycles : C1, C2, C3, C4, C5, C6 C7
If Sub1 has C1, C2, C5 then C3, C4 missing for Sub1 should be in the output
If Sub2 has C1, C2, C3, C7 then C4, C5, C6 missing for Sub2 should be in the output

Re: Find last Cycle for subject and check whether all cycles before that are present or not

PK06 — Mon, 25 Oct 2021 08:05:11 GMT

Dataset
Sub1 C1
Sub1 C2
Sub1 C5
Sub2 C1
Sub2 C2
Sub2 C3
Sub3 C7

Expected Output :
Sub1 C3
Sub1 C4
Sub2 C4
Sub2 C5
Sub2 C6

Re: Find last Cycle for subject and check whether all cycles before that are present or not

Kurt_Bremser — Mon, 25 Oct 2021 08:10:28 GMT

Create a template with all subjects and cycles.

data required;
input cycle $;
datalines;
C1
C2
C3
C4
C5
C6
C7
;

proc sql,
create table template as
  select distinct a.subject, b.cycle
  from have a, required b
;
quit;

Then use this in a MERGE to determine the missing rows.

data want;
merge
  have (in=h)
  template
;
by subject cycle;
if not h;
keep subject cycle;
run;

Untested; for tested code, supply example data in usable form (data step with datalines, posted in a code box).

Re: Find last Cycle for subject and check whether all cycles before that are present or not

Ksharp — Mon, 25 Oct 2021 12:02:02 GMT

data required;
input cycle $;
datalines;
C1
C2
C3
C4
C5
C6
C7
;

data have;
input subject $ cycle $;
cards;
Sub1 C1
Sub1 C2
Sub1 C5
Sub2 C1
Sub2 C2
Sub2 C3
Sub2 C7
;

proc sql;
create table temp as
select subject,
 min(input(compress(cycle,,'kd'),best.)) as min,
 max(input(compress(cycle,,'kd'),best.)) as max
 from have
  group by subject;
quit;
data temp2;
 set temp;
 length cycle $ 20;
 do i=min to max;
   cycle=cats('C',i);output;
 end;
 keep subject cycle;
run;
proc sql;
create table want as
select * from temp2
except 
select * from have;
quit;