topic how flag the first record when 2 values are same when we have with multiple observations in data in SAS Programming

how flag the first record when 2 values are same when we have with multiple observations in data

sahoositaram555 — Tue, 15 Aug 2023 18:47:06 GMT

Hi all,

I have a dataset as below.

data have;
        input col1 col2;
        datalines;
    1001 1.5
    1001 1
    1001 1.5
    1001 1.5
    1001 1.5
    1001 5
    1001 5
    1002 1.5
    1002 4.25
    1002 2.5
    1002 2.5
    1002 2.5
    1002 1.5
    1002 1.5
    1003 6.5
    1003 
    1003 1.5
    1003 1.5
    1003 5
    1003 7
    1003 2.1
    1003 2.5
    1003 2.5 
    ;
    run;

I would like to flag the first record (out of last 2 records by grouping col2 if repetitive).

eg: for 1001 even though there are 1.5 is repeated, my focus is on last 2 observations and flag the 6th observation as 6th and 7th record have same value 5. for 1002, flag the 11th observation, as 11th and 12th rec has the value 1.5.

If last 2 observations are repeated, I would like to flag the previous record to the last record by each col1 group.

Re: how flag the first record when 2 values are same when we have with multiple observations in data

PaigeMiller — Tue, 15 Aug 2023 18:52:12 GMT

What gets flagged when the last two records do not have equal values?

The above question notwithstanding, this is really a simple problem when the last two records are equal, you can flag the LAST one and not the next-to-last one — unless there are more variables involved which might not be equal, you didn't say anything like that however. Is that the case?

Re: how flag the first record when 2 values are same when we have with multiple observations in data

sahoositaram555 — Tue, 15 Aug 2023 18:59:21 GMT

Thanks for responding, there are more variables involved, the intention is to flag the record from where the repetition starts in last 2 recs, so flagging the record prior to last record where repetition started is necessary.

Re: how flag the first record when 2 values are same when we have with multiple observations in data

Astounding — Tue, 15 Aug 2023 19:02:16 GMT

Here's one way:

data want;
   n_recs = 0;
   do until (last.col1);
      set have;
      by col1;
      n_recs + 1;
   end;
   next_to_last = n_recs - 1;
   last_value = col2;
   n_recs = 0;
   do until (last.col1);
      set have;
      by col1;
      n_recs + 1;
      if n_recs = next_to_last and last_value = col2 then flag=1;
      else flag = .;
      output;
   end;
run;

Re: how flag the first record when 2 values are same when we have with multiple observations in data

Tom — Tue, 15 Aug 2023 20:56:59 GMT

Looks like you want to flag the FIRST that is not also the LAST.

data want;
  set have ;
  by col1 col2 notsorted;
  flag = first.col2 and not last.col2;
run;

Obs    col1       col2    flag

  1    1001       1.50      0
  2    1001       1.00      0
  3    1001       1.50      1
  4    1001       1.50      0
  5    1001       1.50      0
  6    1001       5.00      1
  7    1001       5.00      0
  8    1002       1.50      0
  9    1002       4.25      0
 10    1002       2.50      1
 11    1002       2.50      0
 12    1002       2.50      0
 13    1002       1.50      1
 14    1002       1.50      0
 15    1003       6.50      0
 16    1003    1003.00      0
 17    1003       1.50      0
 18    1003       5.00      0
 19    1003       7.00      0
 20    1003       2.10      0
 21    1003       2.50      1
 22    1003       2.50      0