topic Re: If statement in SAS Programming

If statement

ahtinuS — Tue, 08 Apr 2014 11:44:56 GMT

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.

Re: If statement

RW9 — Tue, 08 Apr 2014 12:21:24 GMT

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.

Re: If statement

ahtinuS — Tue, 08 Apr 2014 12:25:45 GMT

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

Re: If statement

RW9 — Tue, 08 Apr 2014 13:19:37 GMT

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.