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;
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;
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
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;
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;
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;
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.
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.
So I had the correct solution but then someone else got the credits. Oh well... 🙂
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.