topic Re: Override values in SAS Programming

Override values

Srigyan — Mon, 10 Sep 2018 08:50:08 GMT

I have the price for 3 product where there is product start date. it means for ID1 from 01-Jan-18 price will be 7.

Product_ID	starting Date	price
ID1	01-Jan-18	7
ID2	05-Jan-18	89
ID3	03-Jan-18	6

I have another table which overrides the older price. it means for the ID1 price has changed as 12 from 03-Jan-2018 and again on 06-Jan-2018.

Product_ID	Overridden date	price
ID1	03-Jan-18	12
ID1	06-Jan-18	3
ID1	07-Jan-18	5
ID2	07-Jan-18	87
ID2	10-Jan-18	99
ID3	04-Jan-18	7
ID3	05-Jan-18	5
ID3	07-Jan-18	8
ID3	11-Jan-18	9

the output table will be like following...

Product_ID	Date	price
ID1	01-Jan-18	7
ID1	02-Jan-18	7
ID1	03-Jan-18	12
ID1	04-Jan-18	12
ID1	05-Jan-18	12
ID1	06-Jan-18	3
ID1	07-Jan-18	5
ID1	08-Jan-18	5
ID1	09-Jan-18	5
ID1	10-Jan-18	5
ID1	11-Jan-18	5
ID1	so on till today()	5
ID2	05-Jan-18	89
ID2	06-Jan-18	89
ID2	07-Jan-18	87
ID2	08-Jan-18	87
ID2	09-Jan-18	87
ID2	10-Jan-18	99
ID2	11-Jan-18	99
ID2	12-Jan-18	99
ID2	so on till today()	99
ID3	03-Jan-18	6
ID3	04-Jan-18	7
ID3	05-Jan-18	5
ID3	06-Jan-18	5
ID3	07-Jan-18	8
ID3	08-Jan-18	8
ID3	09-Jan-18	8
ID3	10-Jan-18	8
ID3	11-Jan-18	9
ID3	so on till today()	9
ID3	13-Jan-18	9

Please share the optimize code...

Re: Override values

RW9 — Mon, 10 Sep 2018 09:03:51 GMT

I am not here to type in test data, post test data in the form of a datastep in future, as such this code is not tested:

data total;
  set table1 (rename=(starting=date)) 
        table2 (rename=(overidden=date));
run;
proc sort data=total;
  by product_id date;
run;
data want;
  set total;
  retain lst_date;
  by product_id;
  if first.product_id then do;
    lst_date=date;
    output;
  end;
  else do;
    do i=1 to overidden-lst_date;
      date=date+i;
      output;
    end;
    lst_date=date;
  end;

Re: Override values

Srigyan — Mon, 10 Sep 2018 11:16:07 GMT

do i=1 to overidden-lst_date;

from where you got overidden

Re: Override values

Astounding — Mon, 10 Sep 2018 11:46:06 GMT

This may not be the fastest code but it should work:

data have1a;

set have1;

do date=starting_date to today();

output;

end;

drop starting_date;

run;

data have2a;

set have2;

do date=overridden_date to today();

output;

end;

drop overridden_date;

run;

proc sort data=have2a;

by product_ID date;

run;

data want;

update have1a have1b;

by product_ID date;

run;

Re: Override values

Srigyan — Mon, 10 Sep 2018 11:52:09 GMT

the number of overrides are not confirmed, so this one does not fulfill the requirement.

Re: Override values

Astounding — Mon, 10 Sep 2018 11:56:08 GMT

What are you talking about? Multiple overrides are part of the incoming data (HAVE2), but the number of overrides does not appear in the final solution data set. If you try the code on your data, it should work perfectly. But you have to try it and see.

Re: Override values

Ksharp — Mon, 10 Sep 2018 13:42:25 GMT

data a;
input Product_ID $ startingDate : date11. price;
format 	startingDate date11.;
cards;
ID1	01-Jan-18	7
ID2	05-Jan-18	89
ID3	03-Jan-18	6
;
run;

data b;
input Product_ID $ startingDate : date11. price;
format 	startingDate date11.;
cards;
ID1	03-Jan-18	12
ID1	06-Jan-18	3
ID1	07-Jan-18	5
ID2	07-Jan-18	87
ID2	10-Jan-18	99
ID3	04-Jan-18	7
ID3	05-Jan-18	5
ID3	07-Jan-18	8
ID3	11-Jan-18	9
;
run;
data have;
 set a b;
 by Product_ID  startingDate;
run;
data want;
 merge have have(firstobs=2 drop=price rename=(Product_ID=_Product_ID startingDate=_startingDate)); 
 output;
 if Product_ID=_Product_ID then do;
   do i= startingDate+1 to _startingDate-1;
     startingDate=i;output;
   end;
 end;
 else do;
   do i= startingDate+1 to today();
     startingDate=i;output;
   end;
 end;
 drop _: i;
 run;

Re: Override values

Srigyan — Tue, 11 Sep 2018 08:49:03 GMT

Instead to today I put startingDate+15;

do i= startingDate+1 to startingDate+15; range is coming for start_date from 1st Jan to 15-Jan is same. though it is correct for override data.

Product_ID	startingDate	price
ID1	01-Jan-18	7
ID1	02-Jan-18	7
ID1	03-Jan-18	7
ID1	04-Jan-18	7
ID1	05-Jan-18	7
ID1	06-Jan-18	7
ID1	07-Jan-18	7
ID1	08-Jan-18	7
ID1	09-Jan-18	7
ID1	10-Jan-18	7
ID1	11-Jan-18	7
ID1	12-Jan-18	7
ID1	13-Jan-18	7
ID1	14-Jan-18	7
ID1	15-Jan-18	7
ID1	16-Jan-18	7
ID1	03-Jan-18	12
ID1	04-Jan-18	12
ID1	05-Jan-18	12
ID1	06-Jan-18	3
ID1	07-Jan-18	5
ID1	08-Jan-18	5
ID1	09-Jan-18	5
ID1	10-Jan-18	5
ID1	11-Jan-18	5
ID1	12-Jan-18	5
ID1	13-Jan-18	5
ID1	14-Jan-18	5
ID1	15-Jan-18	5
ID1	16-Jan-18	5
ID1	17-Jan-18	5
ID1	18-Jan-18	5
ID1	19-Jan-18	5
ID1	20-Jan-18	5
ID1	21-Jan-18	5
ID1	22-Jan-18	5
ID2	05-Jan-18	89
ID2	06-Jan-18	89
ID2	07-Jan-18	89
ID2	08-Jan-18	89
ID2	09-Jan-18	89
ID2	10-Jan-18	89
ID2	11-Jan-18	89
ID2	12-Jan-18	89
ID2	13-Jan-18	89
ID2	14-Jan-18	89
ID2	15-Jan-18	89
ID2	16-Jan-18	89
ID2	17-Jan-18	89
ID2	18-Jan-18	89
ID2	19-Jan-18	89
ID2	20-Jan-18	89
ID2	07-Jan-18	87
ID2	08-Jan-18	87
ID2	09-Jan-18	87
ID2	10-Jan-18	99
ID2	11-Jan-18	99
ID2	12-Jan-18	99
ID2	13-Jan-18	99
ID2	14-Jan-18	99
ID2	15-Jan-18	99
ID2	16-Jan-18	99
ID2	17-Jan-18	99
ID2	18-Jan-18	99
ID2	19-Jan-18	99
ID2	20-Jan-18	99
ID2	21-Jan-18	99
ID2	22-Jan-18	99
ID2	23-Jan-18	99
ID2	24-Jan-18	99
ID2	25-Jan-18	99
ID3	03-Jan-18	6
ID3	04-Jan-18	6
ID3	05-Jan-18	6
ID3	06-Jan-18	6
ID3	07-Jan-18	6
ID3	08-Jan-18	6
ID3	09-Jan-18	6
ID3	10-Jan-18	6
ID3	11-Jan-18	6
ID3	12-Jan-18	6
ID3	13-Jan-18	6
ID3	14-Jan-18	6
ID3	15-Jan-18	6
ID3	16-Jan-18	6
ID3	17-Jan-18	6
ID3	18-Jan-18	6
ID3	04-Jan-18	7
ID3	05-Jan-18	5
ID3	06-Jan-18	5
ID3	07-Jan-18	8
ID3	08-Jan-18	8
ID3	09-Jan-18	8
ID3	10-Jan-18	8
ID3	11-Jan-18	9
ID3	12-Jan-18	9
ID3	13-Jan-18	9
ID3	14-Jan-18	9
ID3	15-Jan-18	9
ID3	16-Jan-18	9
ID3	17-Jan-18	9
ID3	18-Jan-18	9
ID3	19-Jan-18	9
ID3	20-Jan-18	9
ID3	21-Jan-18	9
ID3	22-Jan-18	9
ID3	23-Jan-18	9
ID3	24-Jan-18	9
ID3	25-Jan-18	9
ID3	26-Jan-18	9