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
Are you looking to have the solution work for other combinations of account, month and flag for different month(s)?
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 |
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.