which procedure to get the result

[Ravikumarkummari]

Hi all,

the data is in txt file.

in above the product wise data the week numbers are available. these are in order. consider the min and maximum week number to that particular product and in between any numbers are missing then include that numbers and assign value to zero. i clearly mention in below desired output.

How this process is done.

Your valid comments are welcome.

Thanks in advance.

[data_null__]

If you have SAS/ETS then PROC EXPAND would be the procedure.  If you don't then a simple data step will do the job.

data have;
   infile cards expandtabs;
   input product $  weeks commission  salary;
   cards;
aaa   1  1000  6500
aaa   2  2000  7000
aaa   3  3000  7500
aaa   4  4000  8000
aaa   5  5000  8500
aaa   6  6000  9000
aaa   7  7000  9500
aaa   8  8000  10000
aaa   13 9000  10500
aaa   14 10000 11000
aaa   15 11000 11500
bbb   1  500   8500
bbb   2  1000  9500
bbb   3  1500  10500
bbb   4  2000  11500
bbb   5  2500  12500
bbb   6  3000  13500
bbb   7  3500  14500
bbb   8  4000  15500
bbb   9  4500  16500
bbb   10 5000  17500
bbb   20 5500  18500
bbb   25 6000  19500
bbb   26 6500  20500
bbb   27 7000  21500
bbb   28 7500  22500
bbb   29 8000  23500
bbb   30 8500  24500
ccc   1  9000  25500
ccc   2  9500  26500
ccc   3  10000 27500
ccc   4  10500 28500
ccc   5  11000 29500
ccc   6  11500 30500
ccc   7  12000 31500
ccc   8  12500 32500
ccc   9  13000 33500
ccc   10 13500 34500
ccc   11 14000 35500
ccc   12 14500 36500
ccc   30 15000 37500
ccc   35 15500 38500
ccc   36 16000 39500
ccc   37 16500 40500
ccc   38 17000 41500
ccc   39 17500 42500
ccc   40 18000 43500
ccc   50 18500 44500
;;;;
   run;
proc print;
   run;
data want;
   set have end=eof;
   by product;
   if not eof then set have(firstobs=2 keep=weeks rename=weeks=nextweek);
   if last.product then output;
   else if not last.product then do weeks = weeks to nextweek -1;
      output;
      commission=0; salary=0;
      end;
   run;
proc print;
   run;

[Ravikumarkummari]

Thanks a lot sir.