SAS - replicating the account numbers across the data

Question

I have a data set like this:

AcntNum  name  date
100      abc   12/01
         abc2  12/02
         abc3  12/03
101      abc4  12/04
         abc5  12/05
         abc6  12/06

I am trying to accomplish the below result dataset: Result:

AcntNum  name  date
100      abc   12/01
100      abc2  12/02
100      abc3  12/03
101      abc4  12/04
101      abc5  12/05
101      abc6  12/06

Please suggest what method to follow.

Thanks,

The first resultset looks like report with merged cell. Is date always ascending through groups? So there is no `100 12/07` — Lukasz Szozda, Dec 18 '15 at 20:00
Because of limitations in native `proc sql`, this is more easily done in a data step -- unless the data is already in a database. — Gordon Linoff, Dec 18 '15 at 20:44
Please show what you have tried. As noted by @Gordon, a data step answer is probably easier, are you open to that? — Quentin, Dec 19 '15 at 13:31
Yes, I am okey using Data step. Can I use retain statement/? — user2800204, Dec 19 '15 at 22:27

score 2 · Accepted Answer · answered Dec 19 '15 at 23:54

2

You need to use RETAIN and another variable. Here is one way.

data want;
  set have;
  retain acntnum2;
  acntnum=coalesce(acntnum,acntnum2);
  acntnum2=acntnum;
run;

answered Dec 19 '15 at 23:54

Tom

47,574
2
16
29

much better than my answer, nice – DCR Dec 20 '15 at 13:22

score 0 · Answer 2 · answered Dec 19 '15 at 22:53

there's probably a better way but you could try the following:

data raw1;                                                                                                                              
set raw ;                                                                                                                               
retain group;                                                                                                                           
if( _n_ = 1) then group = 0;                                                                                                            
if (acct ne .) then group = group +1;                                                                                                   
run;                                                                                                                                    



data raw2;                                                                                                                              
set raw1;                                                                                                                               
retain acct2;                                                                                                                           
by group;                                                                                                                               
if first.group then acct2 = acct;                                                                                                       
run;

SAS - replicating the account numbers across the data

2 Answers2