topic Renumbering with a condition in SAS Programming

Renumbering with a condition

soumri — Wed, 01 Jun 2016 15:10:30 GMT

hi evry body;

what i have:

id na dc V

S1 1 d1 12

S1 1 d2 11

S1 1 d3 11

S1 2 d5 11

S1 2 d6 12

S3 2 d7 10

S3 2 d8 11

S3 3 d9 9

S3 3 d10 10

S3 3 d11 10

S4 1 d12 9

S4 2 d13 9

what i want:

id na dc V num

S1 1 d1 12 1

S1 1 d2 11 2

S1 1 d3 11 3

S1 2 d5 11 1

S1 2 d6 12 2

S3 2 d7 10 1

S3 2 d8 11 2

S3 3 d9 9 1

S3 3 d10 10 2

S3 3 d11 10 3

S3 3 d12 9 4

S4 1 d13 9 1

S4 2 d14 9 2

i do this:

if first.id & na then num=0;nc+1;

but as a result i have:

id na dc V num

S1 1 d1 12 1

S1 1 d2 11 2

S1 1 d3 11 3

S1 2 d5 11 4

S1 2 d6 12 5

S3 2 d7 10 1

S3 2 d8 11 2

S3 3 d9 9 3

S3 3 d10 10 4

S3 3 d11 10 5

S3 3 d12 9 6

S4 1 d13 9 1

S4 2 d14 9 2

with is wrong;

can some one help please;

Re: renumbring with a condition

ballardw — Wed, 01 Jun 2016 14:57:15 GMT

You will have to supply the rule as to WHY the count resets at the indicated locations:

id na dc V num

S1 1 d1 12 1

S1 1 d2 11 2

S1 1 d3 11 3

S1 2 d5 11 1 /* this looks like it resets because of change of na variable?*/

S1 2 d6 12 2

S3 2 d7 10 1 /* why here?*/

S3 2 d8 11 2

S3 3 d9 9 1

S3 3 d10 10 2

S3 3 d11 10 3

S3 3 d12 9 4

S4 1 d13 9 1

S4 2 d14 9 2

Re: renumbring with a condition

jklaverstijn — Wed, 01 Jun 2016 14:59:14 GMT

Hi @soumri,

You reset the counter at every new group defined by ID. What you apparently want is to reset for every ID+NA.

So your rule would be

if first.id and first.na

I think this can be shortcircuited to "if first.na" but have not tested that.

I think (code is missing) that you have "if first.id and na". In this rule and with your data " if na" is always true.

Hope this helps,

- Jan.

Re: renumbring with a condition

soumri — Wed, 01 Jun 2016 15:08:10 GMT

The problem is:
for an ID, we can get at na variable 1, 2 or 3 as a value.
So the variable num resets are because of change of ID (thousants), or of na variable;

Re: renumbring with a condition

Reeza — Wed, 01 Jun 2016 15:14:49 GMT

Include two variables in your BY statement and increment based on that.

Data want;
Set have;
By Id na;

If first.na then count=1;
Else count+1;

Run;

http://www.ats.ucla.edu/stat/sas/faq/enumerate.htm

Re: renumbring with a condition

Reeza — Wed, 01 Jun 2016 15:14:52 GMT

Include two variables in your BY statement and increment based on that.

Data want;
Set have;
By Id na;

If first.na then count=1;
Else count+1;

Run;

http://www.ats.ucla.edu/stat/sas/faq/enumerate.htm

Re: renumbring with a condition

soumri — Wed, 01 Jun 2016 15:21:35 GMT

Yes, thank you.

Re: renumbring with a condition

Tom — Wed, 01 Jun 2016 15:22:09 GMT

Yes. When you have a series of BY variables, the FIRST/LAST flags are nested. So if it is the first (or last) for a higher term in the BY variable list is will be definition also be FIRST (or LAST) for each sub term.

So if your by variables are ID and NA when it is FIRST.ID is also FIRST.NA, even if the value for NA did not change from the last value of NA for the previous value of ID.

Re: renumbring with a condition

soumri — Wed, 01 Jun 2016 15:26:08 GMT

hi @jklaverstijn, Yes, I confirm that it is not necessary to set if first.id. bcp thank you for the explanation.

Re: renumbring with a condition

jklaverstijn — Wed, 01 Jun 2016 15:37:28 GMT

So I had the correct solution but then someone else got the credits. Oh well... 🙂

Re: renumbring with a condition

Reeza — Wed, 01 Jun 2016 18:10:05 GMT

@jklaverstijn You were close, you missed mentioning that the BY statement should change as well, though one could assume the OP knew this. Sometimes it's a crapshoot which answer gets marked correct. I just had one assigned to me, when it belong to someone else, and I had to ask to have it corrected.