For each id if acct1 and acct2 have values repeated then how to set them to blank?
id acct1 Acct2
11 12345 12345
11 12345 45678
11 12345 45678
11 23456 23456
12 45678 23456
12 45678 45678
12 56789 56789
11 12345 12345
11 45678
11
11 23456 23456
12 45678 23456
12 45678
12 56789 56789
Hi SASPhile,
proc sort data=inputds out=sortedds;
by id acct1;
data outputds;
set sortedds;
lag_acct1 = lag(acct1);
if ^first.id then
if acct1 = lag_acct1 then acct1 = .;
drop lag_acct1;
run;
And you do the same for acct2.
If the acct1 and acct2 pair doesn't matter, you could simply produce 2 output datasets.
I hope this helps.
Yoan
I'm not so sure this is a good idea, but as long as your data are sorted as indicated in the BY statement, it's pretty easy to accomplish:
data want;
set have;
by id acct1 acct2;
if first.acct1=0 then call missing(acct1);
if first.acct2=0 then call missing(acct2);
run;
If the data aren't necessarily sorted, you can add the word NOTSORTED at the end of the BY statement.
OK.
data have; input id acct1 Acct2 ; cards; 11 12345 12345 11 12345 45678 11 12345 45678 11 23456 23456 12 45678 23456 12 45678 45678 12 56789 56789 ; run; data want; set have; if id=lag(id) and acct1=lag(acct1) then call missing(acct1); if id=lag(id) and acct2=lag(acct2) then call missing(acct2); run;
Ksharp
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.