topic Re: Fill rows with sequential data in SAS Enterprise Guide

Fill rows with sequential data

MM88 — Tue, 08 Aug 2023 15:24:45 GMT

Hi community,

I am new to SAS, I would like to obtain a SAS dataset that completes intermediate lines.

I have a dataset like:

data original_data;
    input var1 $ var2;
    datalines;
A 12
B 15
C 20
D 23
;
run;

I would like to obtain another dataset with the intermediate lines like:

data original_data;
    input var1 $ var2;
    datalines;
A 12
A 13
A 14
B 15
B 16
B 17
B 18
B 19
C 20
C 21
C 22
D 23
;
run;

Thanks for your help.

Re: Fill rows with sequential data

Tom — Tue, 08 Aug 2023 15:36:18 GMT

If would be easier if you reversed the order because it is easier to REMEMBER something than PREDICT the future.

data original_data;
    input var1 $ var2;
datalines;
A 12
B 15
C 20
D 23
;

proc sort data=original_data out=want;
  by descending var2;
run;

data want;
  set want;
  by descending var2;
  do var2=var2 to max(var2,lag(var2)-1);
    output;
  end;
run;

proc sort data=want;
  by var2;
run;

proc print;
run;

The LAG() function let's you remember the value it was last passed.

Looking into the future is harder. Here is one way:

data want;
  set original_data;
  set original_data(keep=var2 rename=(var2=next_var2) firstobs=2) original_data(drop=_all_ obs=1);
  do var2=var2 to max(var2,next_var2-1);
    output;
  end;
  drop next_var2;
run;

Re: Fill rows with sequential data

MM88 — Tue, 08 Aug 2023 15:43:15 GMT

Thanks @Tom . It works good. I appreciate your help.

Re: Fill rows with sequential data

sbxkoenk — Tue, 08 Aug 2023 15:55:11 GMT

data original_data;
    input var1 $ var2;
    datalines;
A 12
B 15
C 20
D 23
;
run;

data have;
 merge original_data 
       original_data(firstobs=2 
                     rename=(var1=var1accent var2=var2accent));
run;

data want(drop=var1accent var2: rename=(i=var2));
 set have end=last;
 if var2accent NE . then do;
   do i=var2 to (var2accent-1);
    output;
   end;
 end;
 if last then do; i=var2; output; end;
run;
/* end of program */

Koen