topic Re: Conditional updating column value in SAS Programming

Conditional updating column value

micksom — Wed, 17 Sep 2014 19:03:33 GMT

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

Re: Conditional updating column value

ballardw — Wed, 17 Sep 2014 19:16:34 GMT

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

Re: Conditional updating column value

micksom — Wed, 17 Sep 2014 19:23:10 GMT

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

Re: Conditional updating column value

PGStats — Wed, 17 Sep 2014 19:42:59 GMT

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;

Re: Conditional updating column value

Haikuo — Wed, 17 Sep 2014 19:43:18 GMT

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

Re: Conditional updating column value

micksom — Wed, 17 Sep 2014 20:28:29 GMT

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