BookmarkSubscribeRSS Feed
Nsas
Calcite | Level 5

I wanna count the consecutive rows (>=3) with RUN=0 between rows with RUN=10. 

Have:

data have;
input  ID day run;
datalines;
1 0 0
1 2 10
1 2 0
1 3 10
1 4 0
1 4 0
1 5 0
1 7 0
1 7 0
1 10 0
1 11 10
1 12 0
1 14 0
1 15 0
1 16 10
1 17 0
1 18 0
1 19 0
1 20 0
1 21 10
1 22 0
1 23 0
1 24 0
1 25 0
1 26 10
1 27 0
1 28 0
1 29 10
1 30 0
;;

want:

 

IDDAYRUNCOUNT
1001
1210.
1201
1310.
1402
1402
1502
1702
1702
11002
11110.
11203
11403
11503
11610.
11704
11804
11904
12004
12110.
12205
12305
12405
12505
12610.
12701
12801
12910.
13001

 

I do appreciate any tips to get this output.

Note: All consecutive rows with RUN=0 and  day <10 will be assigned the same value.

6 REPLIES 6
Patrick
Opal | Level 21

Is below a possible scenario and if yes how would the count look like??

 

1 0 0
1 2 10
1 2 0
1 3 0
1 3 10
1 4 0
1 4 0
1 5 0
1 7 0
1 7 0

 

Nsas
Calcite | Level 5

Count would be:

1

.

1

1

 .

2

2

2

2

2

 

Patrick
Opal | Level 21

Here a way to go:

data have;
input  ID day run;
datalines;
1 0 0
1 2 10
1 2 0
1 3 10
1 4 0
1 4 0
1 5 0
1 7 0
1 7 0
1 10 0
1 11 10
1 12 0
1 14 0
1 15 0
1 16 10
1 17 0
1 18 0
1 19 0
1 20 0
1 21 10
1 22 0
1 23 0
1 24 0
1 25 0
1 26 10
1 27 0
1 28 0
1 29 10
1 30 0
;
run;

data inter1 
     HaveWithGroup(keep=ID day run group);
  set have;
  by run notsorted;

  if first.run and run ne 10 then
    do;
      _group+1;
    end;

  if run=10 then call missing(group);
  else group=_group;

  if run=10 then group_cnt=.;
  else group_cnt+1;

  output HaveWithGroup;

  if last.run and run ne 10 then 
    do;
      if group_cnt<3 then count=1;
      else count+1;
      output inter1;
    end;

run;

data want;
  set HaveWithGroup;
  call missing(count);
  if _n_=1 then 
    do;
      dcl hash h1(dataset:'inter1(keep=group count)', multidata:'n');
      h1.defineKey('group');
      h1.defineData('count');
      h1.defineDone();
    end;
  if run ne 10 then h1.find();
run;

mkeintz
PROC Star

You want to find all instances of 3+ consecutive zeroes between 10's.  When such a series is found you increment the sequence number by 1.  But if a shorter series is found you reset the sequence number to 1.  Is that correct?

 

data want (drop=nzero seq);
  do Nzero=0 by 1 until (run=10 or eod);
    set have end=eod;
  end;
  if nzero>=3 then seq+1;
  else seq=1;

  count=seq;
  do until(run=10 or eod2);
    set have end=eod2;
    if run=10 then count=.;
    output;
  end;
run;

Notes:

  1. This program assumes that run=10 or 0 - no other values appear, as in your sample data.
  2. The "do nzero=" loop starts at zero rather than one, so that when the "run=10" record countributes to the increment of nzero, it does not produce an overcount.
  3. I include the "or eod" and "or eod2" in the "until" conditions to accomodate the possibility that the last record of the dataset does not have run=10.

 

If you have run values other than 10 and 0, then counting the consecutive records between 10's is no longer the same as counting "run=0" records.  You'd have to modify the program to this:

 

data want (drop=threezeroesfound seq);
  do until (run=10 or eod);
    set have end=eod;
    if lag2(run)=0 and lag(run)=0 and run=0 then threezeroesfound=1;
  end;
  if threezeroesfound then seq+1;
  else seq=1;
  
  count=seq;
  do until(run=10 or eod2);
    set have end=eod2;
    if run=10 then count=.;
    output;
  end;
run;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ari
Quartz | Level 8 ari
Quartz | Level 8

@mkeintz:

Thanks.

mkeintz
PROC Star
I'm not sure I fully comprehend. Please show some data before and after using the new criterion.
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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!

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
  • 6 replies
  • 2723 views
  • 3 likes
  • 4 in conversation