Need to create a flag variable based on Ref and ID when UNIT is different.

AwesomeSAS — Thu, 28 Dec 2023 19:11:21 GMT

When Ref = 10 for particular ID = 1 then UNIT is expected to be same as the first record.

In below scenario, when Ref = 20 we see different value as C in 2nd row.

Expectation is to create a Flag variable when we have different values compared to 1st record within a same Ref and ID.

Ref ID UNIT

10 1 C

20 1 Kg

20 1 C

10 2 Kg

10 2 C

Re: Need to create a flag variable based on Ref & ID when UNIT is different.

Tom — Thu, 28 Dec 2023 19:15:07 GMT

Not sure how you can tell which is "first" since there does not seem to a date or other ordering variable. And your listing is NOT currently sorted by REF and ID.

But assuming that you haven't dropped the card deck somewhere along the way and you want to process the groups as they appear in the data you can use the NOTSORTED keyword on the BY statement to tell SAS to create the by groups based on the current ordering.

First let's convert your listing into an actual SAS dataset so we have something to program against.

data have;
  input Ref ID UNIT $;
cards;
10 1 C
10 1 C
10 1 C
20 1 Kg
20 1 C
10 2 Kg
10 2 C
10 2 C
;

Now just use BY group processing and a new RETAINed variable to remember the first value. Then we can compare the two variables to make a new "flag" variable.

data want;
  set have;
  by ref id notsorted;
  if first.id then first_unit=unit;
  retain first_unit;
  flag = (unit = first_unit);
run;

Result

                            first_
OBS    Ref    ID    UNIT     unit     flag

 1      10     1     C        C         1
 2      10     1     C        C         1
 3      10     1     C        C         1
 4      20     1     Kg       Kg        1
 5      20     1     C        Kg        0
 6      10     2     Kg       Kg        1
 7      10     2     C        Kg        0
 8      10     2     C        Kg        0

If that is not the output you wanted then provide a more complete description of the algorithm and the output you expect for the sample input you provided.

Re: Need to create a flag variable based on Ref & ID when UNIT is different.

mkeintz — Fri, 29 Dec 2023 04:49:33 GMT

"dropped the card deck"?

How long it has been!

topic Re: Need to create a flag variable based on Ref &amp; ID when UNIT is different. in SAS Programming

Need to create a flag variable based on Ref and ID when UNIT is different.

Re: Need to create a flag variable based on Ref & ID when UNIT is different.

Re: Need to create a flag variable based on Ref & ID when UNIT is different.

topic Re: Need to create a flag variable based on Ref & ID when UNIT is different. in SAS Programming