BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
gahlot1999
Fluorite | Level 6

Hi Community,

Can anyone please help me with this logic?

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

Source Dataset:

SubjectCycle
1Cycle 1
1Cycle 1
1Cycle 2
1Com Cycle 1
1Com Cycle 2
1Com Cycle 3
1Brid Cycle 1
1Brid Cycle 1
1Brid Cycle 1
2Ind Cycle 1
2Ind Cycle 1
2Ind Cycle 2
2Com Cycle 1
2Com Cycle 1
2Com Cycle 3
2Brid Cycle 1
2Brid Cycle 1
2Brid Cycle 3

Output Data set:

SubjectCycleCycle_no
1Cycle 1Cycle 1
1Cycle 1Cycle 1
1Cycle 2Cycle 2
1Com Cycle 1Cycle 3
1Com Cycle 2Cycle 4
1Com Cycle 3Cycle 5
1Brid Cycle 1Cycle 6
1Brid Cycle 1Cycle 6
1Brid Cycle 1Cycle 6
2Ind Cycle 1Cycle 1
2Ind Cycle 1Cycle 1
2Ind Cycle 2Cycle 2
2Com Cycle 1Cycle 3
2Com Cycle 1Cycle 3
2Com Cycle 3Cycle 4
2Brid Cycle 1Cycle 5
2Brid Cycle 1Cycle 5
2Brid Cycle 3Cycle 6
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller

View solution in original post

4 REPLIES 4
gahlot1999
Fluorite | Level 6

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

PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller
FreelanceReinh
Jade | Level 19

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
Super User
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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 535 views
  • 1 like
  • 4 in conversation