DATA Step, Macro, Functions and more

Renumbering with a condition

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

Renumbering with a condition

[ Edited ]

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;

 

 

 

 


Accepted Solutions
Solution
‎06-01-2016 11:22 AM
Super User
Posts: 19,820

Re: renumbring with a condition

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

View solution in original post


All Replies
Super User
Posts: 11,343

Re: renumbring with a condition

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

Contributor
Posts: 65

Re: renumbring with a condition

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;

Solution
‎06-01-2016 11:22 AM
Super User
Posts: 19,820

Re: renumbring with a condition

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

Contributor
Posts: 65

Re: renumbring with a condition

Yes, thank you.
Super User
Posts: 19,820

Re: renumbring with a condition

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

Super Contributor
Posts: 441

Re: renumbring with a condition

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. 

Super User
Super User
Posts: 7,059

Re: renumbring with a condition

Posted in reply to jklaverstijn

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.

Contributor
Posts: 65

Re: renumbring with a condition

Posted in reply to jklaverstijn
hi @jklaverstijn, Yes, I confirm that it is not necessary to set if first.id. bcp thank you for the explanation.
Super Contributor
Posts: 441

Re: renumbring with a condition

So I had the correct solution but then someone else got the credits. Oh well... :-) 

Super User
Posts: 19,820

Re: renumbring with a condition

Posted in reply to jklaverstijn

@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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 399 views
  • 5 likes
  • 5 in conversation