Conditional updating column value

[micksom]

Account	Month	Flag
A1	1	0
A1	2	1
A1	3	0
B1	1	0
B2	2	0
B3	1	1

Hi all, hopefully an easy one. Sorry this is a bit simple. I am a new SAS user!!

Is there a way to do this preferably in proc sql (but data step is ok) without creating a new column i.e. as an update function. I am just looking to make a quick fix to an existing dataset?

Where Account = A1 and month = 2 and Flag = 1 then update flag to 1 on Account A1 where month = 1.

Kind Regards,

Michael

[ballardw]

Are you looking to have the solution work for other combinations of account, month and flag for different month(s)?

[micksom]

Yes, exactly. So in the example below I would want

Where Account = A1 and month = 2 and Flag = 1 then update flag to 1 on Account A1 where month = 1.

Where Account = A1 and month = 5 and Flag = 1 then update flag to 1 on Account A1 where month = 0.

I would then need to do the same for multiple other accounts.

OLD

Account	Month	Flag
A1	1	0
A1	2	1
A1	3	0
A1	4	0
A1	5	1
B1	1	0
B1	2	1
B1	3	0
B1	4	0
B1	5	0

NEW

Account	Month	Flag
A1	1	1
A1	2	1
A1	3	0
A1	4	1
A1	5	1
B1	1	1
B1	2	1
B1	3	0
B1	4	0
B1	5	0

[PGStats]

It can be done with SQL UPDATE. You will get a warning, but you can ignore it.

proc sql UNDO_POLICY=NONE;

update MYDATASET as A set Flag = 1

where exists (select * from MYDATASET where Account=A.Account and Month = A.Month+1 and Flag);

quit;

PG

[Haikuo]

Here is one proc sql approach, and I can vision 2XDOW or Hash for data step solution:

DATA HAVE;

INFILE CARDS DLM='09'X;

INPUT Account $ Month Flag @@;

CARDS;

A1   1    0

A1   2    1

A1   3    0

A1   4    0

A1   5    1

B1   1    0

B1   2    1

B1   3    0

B1   4    0

B1   5    0

;

PROC SQL;

SELECT ACCOUNT, MONTH, CASE WHEN EXISTS (SELECT * FROM HAVE WHERE ACCOUNT=A.ACCOUNT AND MONTH=A.MONTH+1 AND FLAG=1) THEN 1 ELSE FLAG END AS FLAG

FROM HAVE A

;QUIT;

Regards,

Haikuo

[micksom]

Thanks for the help guys. Just trying how to work out how to put it into practice.

Two questions:

1. Generally is it possible to do a where statement that will filter all those account which match two criteria  e.g. where account = a.account AND(month = 1 and flag = 0) AND (where month = 2 and flag = 1)

2. In the example provided above MONTH=A.MONTH+1 ,would that still work ok when i have 201203

Michael