topic Re: Repeat value set to blank in SAS Programming

Repeat value set to blank

SASPhile — Wed, 12 Jun 2013 20:39:09 GMT

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 23456 23456

12 45678 23456

12 45678 45678

12 56789 56789

11 12345 12345

11 45678

11 23456 23456

12 45678 23456

12 45678

12 56789 56789

Re: Repeat value set to blank

yoanbolduc — Wed, 12 Jun 2013 21:03:33 GMT

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

Re: Repeat value set to blank

Astounding — Wed, 12 Jun 2013 21:07:01 GMT

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.

Re: Repeat value set to blank

Ksharp — Mon, 17 Jun 2013 06:02:48 GMT

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