DATA Step, Macro, Functions and more

Repeat value set to blank

Reply
Super Contributor
Posts: 673

Repeat value set to blank

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

Occasional Contributor
Posts: 11

Re: Repeat value set to blank

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

Super User
Posts: 5,499

Re: Repeat value set to blank

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.

Super User
Posts: 10,023

Re: Repeat value set to blank

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

Ask a Question
Discussion stats
  • 3 replies
  • 232 views
  • 0 likes
  • 4 in conversation