DATA Step, Macro, Functions and more

Conditional updating column value

Reply
Occasional Contributor
Posts: 9

Conditional updating column value

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

Super User
Posts: 10,516

Re: Conditional updating column value

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

Occasional Contributor
Posts: 9

Re: Conditional updating column value

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

AccountMonthFlag
A110
A121
A130
A140
A151
B110
B121
B130
B140
B150


NEW

AccountMonthFlag
A111
A121
A130
A141
A151
B111
B121
B130
B140
B150
Respected Advisor
Posts: 4,654

Re: Conditional updating column value

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

PG
Respected Advisor
Posts: 3,124

Re: Conditional updating column value

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

Occasional Contributor
Posts: 9

Re: Conditional updating column value

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

Ask a Question
Discussion stats
  • 5 replies
  • 362 views
  • 0 likes
  • 4 in conversation