First and last function sas

Question

I have some data which looks like this

data example1;     
   input Activity $ logflag;
   Activity1 1
   Activity2 1
   Activity3 1
   Activity4 1
   Activity1 2
   Activity2 2
   Activity3 2
   Activity1 3
   Activity2 3
   Activity3 3
   Activity4 3
   Activity1 4
   Activity2 4
   ; 
run;

Where basically the variable 'logflag' increments by 1 every time the 'Activity' returns to 'Activity1' however I want to get to this;

data example2;     
   input Activity $ logflag count;
   Activity1 1 1
   Activity2 1 2
   Activity3 1 3
   Activity4 1 4
   Activity1 2 1
   Activity2 2 2
   Activity3 2 3
   Activity1 3 1
   Activity2 3 2
   Activity3 3 3
   Activity4 3 4
   Activity1 4 1
   Activity2 4 2
   ;
run;

Whereby I have a 'count' which increments by 1 every time a new 'Activity' appears within a certain 'logflag'.

what I am using is this;

data AS2.TENMAY_EXAMPLE4;
  set AS2.TENMAY_SESSIONID;
  by logflag Activity notsorted;
  if first.logflag then count=0;
  if first.Activity then count+1;
run;

and I am getting this

data example2;     
   input Activity $ logflag count;
   Activity1 1 1
   Activity2 1 2
   Activity3 1 2
   Activity4 1 2
   Activity1 2 1
   Activity2 2 2
   Activity3 2 2
   Activity1 3 1
   Activity2 3 2
   Activity3 3 2
   Activity4 3 2
   Activity1 4 1
   Activity2 4 2
   ;
run;

What I cant understand is why the counter increments by 1 then goes to 2 but then never gets to 3 or higher. I am sure I had this working before but I can't work out what I have changed.

Would anyone be able to help with this?

Thanks,

Check and make sure that your input dataset does NOT already have the variable COUNT. — Tom, Jul 05 '16 at 12:38
Hi @Tom. Thank you. This was in fact the issue. Why should it matter that my input already contains a variable called COUNT? — Taylrl, Jul 05 '16 at 15:00

score 1 · Accepted Answer · answered Jul 05 '16 at 16:02

You must already have a variable named COUNT in the input dataset. So each time the SET statement runs the value from the input dataset overwrites the value from the previous observation.

To get your example then COUNT is probably 1 for every observation. So that when you increment when ACTIVITY changes it goes to 2. The value for the first observation per LOGFLAG group is 1 since you first set it to 0 before incrementing it.

andrey_sz · Answer 2 · 2016-07-05T10:39:12.053

0

Input data:

data example1;
   attrib Activity format = $20.
         logflag  format = 8.;
   input Activity $ logflag;
   Activity1 1
   Activity2 1
   Activity3 1
   Activity4 1
   Activity1 2
   Activity2 2
   Activity3 2
   Activity1 3
   Activity2 3
   Activity3 3
   Activity4 3
   Activity1 4
   Activity2 4
   ; 
run;

To take the results what you want try this:

data example2;
   set example1;
   by logflag Activity notsorted;
   if first.logflag then count=1;
   else count+1;
run;

The result is:

Activity   logflag  count
---------  -------  -----
Activity1  1        1
Activity2  1        2
Activity3  1        3
Activity4  1        4
Activity1  2        1
Activity2  2        2
Activity3  2        3
Activity1  3        1
Activity2  3        2
Activity3  3        3
Activity4  3        4
Activity1  4        1
Activity2  4        2

edited Jul 05 '16 at 10:39

answered Jul 05 '16 at 10:15

andrey_sz

751
1
13
29

I ran this code and it still gave me the same thing, whereby the count wont increment any higher than 2. I can see that your code here should work, as should the code I used above. I am baffled. – Taylrl Jul 05 '16 at 10:20
@Taylrl, it's a fantastic :) – andrey_sz Jul 05 '16 at 10:47
Something very strange is going on here. My results are different to yours. I shall investigate and report back :-S – Taylrl Jul 05 '16 at 10:52

score 0 · Answer 3 · answered Jul 05 '16 at 10:25

0

You are missing the retain statement. Every time the datastep loops the variable is reset. Retain suppresses this behavior.

data AS2.TENMAY_EXAMPLE4;
  set AS2.TENMAY_SESSIONID;
  retain count;
  by logflag Activity notsorted;
  if first.logflag then count=0;
  count+1;
run;

answered Jul 05 '16 at 10:25

Jetzler

787
3
11

Thanks @Jetzler however even with this `retain` statement I am still getting the same thing whereby the count won't increment past 2. All of the solutions here I would have thought should have worked. There must be something we are missing – Taylrl Jul 05 '16 at 10:30
1

Yes, right. Just checked it. Using a sum function `x + y;` has a build in retain. So it should have worked all along. Therefore the problem has to be in your input data or a setting on you system. – Jetzler Jul 05 '16 at 11:21

First and last function sas

3 Answers3