If statement

[ahtinuS]

Good day,

I require help with the following:

1. For the 1st instance of A, if B > 0 then set C to 1 else set to 0.

2. For the next (2nd) instance of A, if B > 0 increase C by 1.

3. For the 3rd instance of A, if B to <= 0 reset C to 0

I am not comfortable writing code and prefer to use the query builder but do not know how to tackle this rule.

I think that I should introduce X to count the number of times that A appears and create my rules based on that.

eg.

A     X     B     C

a     1      2     1

b     1      1     1

c     1     -2     0

a     2      3     2

c     2      4     1

a     3      0     0

(Assuming I have followed the correct logic as per my rules.

Firstly, I do not know how to count how many times a variable appears in a column.

Secondly, I do not know how to build this query.

Your assistance will be greatly appreciated.
    

[RW9]

Hi,

There isn't enough information there to do it right at the moment.  You need to identify a sort order first, i.e. what makes a, B=2 appear prior to the record a, b=3.  

[ahtinuS]

Apologies, there is a sort criteria. There is another variable that must be sorted in ascending order prior to the rules.

eg. SORT TABLE  ABC ON  FIELD XYZ in ascending order

[RW9]

Pretty straightforward in Base SAS then:

data have;

attrib A format=$1. X B C format=best.;

infile datalines;

input a $ x b c;

datalines;

a     1      2     1

b     1      1     1

c     1     -2     0

a     2      3     2

c     2      4     1

a     3      0     0

;

run;

proc sort data=have;

  by a x;

run;

data inter;

  set have;

  by a;

  retain result;

  if first.a and b > 0 then result=1;

  else do;

    if b > 0 then result=result+1;

    else result=0;

  end;

run;

As for doing it in a query then that is a bit more tricky as there is no retain options.