Getting cycle number as per subject and cycle name

[gahlot1999]

Hi Community,

Can anyone please help me with this logic?

For each subject, whenever cycle name changes I want Cycle = OldCycle+1.

Source Dataset:

Subject	Cycle
1	Cycle 1
1	Cycle 1
1	Cycle 2
1	Com Cycle 1
1	Com Cycle 2
1	Com Cycle 3
1	Brid Cycle 1
1	Brid Cycle 1
1	Brid Cycle 1
2	Ind Cycle 1
2	Ind Cycle 1
2	Ind Cycle 2
2	Com Cycle 1
2	Com Cycle 1
2	Com Cycle 3
2	Brid Cycle 1
2	Brid Cycle 1
2	Brid Cycle 3

Output Data set:

Subject	Cycle	Cycle_no
1	Cycle 1	Cycle 1
1	Cycle 1	Cycle 1
1	Cycle 2	Cycle 2
1	Com Cycle 1	Cycle 3
1	Com Cycle 2	Cycle 4
1	Com Cycle 3	Cycle 5
1	Brid Cycle 1	Cycle 6
1	Brid Cycle 1	Cycle 6
1	Brid Cycle 1	Cycle 6
2	Ind Cycle 1	Cycle 1
2	Ind Cycle 1	Cycle 1
2	Ind Cycle 2	Cycle 2
2	Com Cycle 1	Cycle 3
2	Com Cycle 1	Cycle 3
2	Com Cycle 3	Cycle 4
2	Brid Cycle 1	Cycle 5
2	Brid Cycle 1	Cycle 5
2	Brid Cycle 3	Cycle 6

[gahlot1999]

I have attached excel also, for source and output dataset.

[PaigeMiller · Accepted solution]

Thank you but we cannot work with Excel files, most people refuse to download any Microsoft Office files as they can be a security risk. In the future, please provide your data as working SAS data step code which you can type in yourself, or follow these instructions.

/* UNTESTED CODE */


data want;
    set have;
    by subject;
    prev_cycle=lag(cycle);
    if first.subject then cycle_no=1;
    if cycle^=prev_cycle and not first.subject then cycle_no+1;
    drop prev_cycle;
run;

[FreelanceReinh]

Hi @gahlot1999,

Minor addition to the solution you've already received:

If you really need the (actually redundant) word "Cycle" in front of each cycle number, you can create a picture format:

proc format;
picture cycle_no (default=10)
low-high='0000' (prefix='Cycle ');
run;

Then just insert the FORMAT statement

format cycle_no cycle_no.;

into Paige Miller's DATA step (e.g., before the RUN statement) to get exactly the output shown in your post, leaving variable CYCLE_NO numeric, as it should be.

[Ksharp]

data have;
input Subject Cycle $20.;
cards;
1 Cycle 1
1 Cycle 1
1 Cycle 2
1 Com Cycle 1
1 Com Cycle 2
1 Com Cycle 3
1 Brid Cycle 1
1 Brid Cycle 1
1 Brid Cycle 1
2 Ind Cycle 1
2 Ind Cycle 1
2 Ind Cycle 2
2 Com Cycle 1
2 Com Cycle 1
2 Com Cycle 3
2 Brid Cycle 1
2 Brid Cycle 1
2 Brid Cycle 3
;

data want;
 set have;
 by Subject Cycle notsorted;
 if first.Subject then n=0;
 n+first.Cycle;
 Cycle_no=catx(' ','Cycle',n);
 drop n;
run;