Update charge amount based on different timeline and multiply factors?

[ybz12003]

Hello, 

I would like to update the "ED_charge / INP_charge / ICU_charge" in the sample dataset below (1st SAS code) based on the different timelines and IF factors (2nd SAS code).  For example, the new "ED_charge_ / INP_charge_ / ICU_charge_" columns will all multiply by 1.25 because the test date is in the range between 01JAN2016'<= TestDate <= '31MAR2016'.  Please help me to approach it; thanks.

data charge;
input ID $ TestDate:ddmmyy10. ED_charge INP_charge ICU_charge;
format TestDate ddmmyy10.;
datalines;
LLL 01/04/2016 1780 1200 4000
UUU 03/09/2017 3000 5000 8000
OOO 09/08/2018 5900 2400 5000
;
run;

if '01JAN2016'd <= TestDate <= '31MAR2016'd then IF= 1.25;
if '01MAR2017'd <= TestDate <= '31MAY2017'd then IF= 1.24;
if '01SEP2018'd <= TestDate <= '31DEC2018'd then IF= 1.23;

[ybz12003]

In addition, the real dataset includes over ten different categories of charges and a dozen different conditions IF factors. Is it good to do an array or loop?

[Kurt_Bremser · Accepted solution]

Keep the begindates, enddates and factors in a separate dataset.

At _N_ = 1 in a data step, read the dataset into an array indexed by dates:

array factors {'01jan1900'd:'31dec2099'd} _temporary_;
if _n_ = 1 then do until (done);
  set factors end=done;
  do _n_ = begindate to enddate;
    factors{_n_} = if;
  enđ;
end;

Later in the code, you can then retrieve the factor for every testdate from the array.

Edit: added _TEMPORARY_ option to ARRAY statement.

[ybz12003]

I'm wondering where I should add your codes.

[Kurt_Bremser]

Quote from my post:

At _N_ = 1 in a data step, 

[ybz12003]

%macro Inflation (Date1, Date2, IF);
data Charge_update;
	set charge;

	if '&Date1.'d <= TestDate <= '&Date2.'d then do;
	Inflation_Factor= &IF.;
	array vars ED_charge INP_charge ICU_charge;
	array vars_ ED_charge_ INP_charge_ ICU_charge_;
	do i=1 to dim(vars);
		vars_(i)=vars(i)*Inflation_Factor;
	end;
	end;

	drop i;

run;
%mend;
%Inflation (01JAN2016,31MAR2016,1.25);

[ybz12003]

I created a code. However, I couldn't get the macro date to work.

127  %macro Inflation (Date1, Date2, IF);
128  data Charge_update;
129      set charge;
130
131      if '&Date1.'d <= TestDate <= '&Date2.'d then do;
132      Inflation_Factor= &IF.;
133      array vars ED_charge INP_charge ICU_charge;
134      array vars_ ED_charge_ INP_charge_ ICU_charge_;
135      do i=1 to dim(vars);
136          vars_(i)=vars(i)*Inflation_Factor;
137      end;
138      end;
139  /*  if '01MAR2017'd <= TestDate <= '31MAY2017'd then IF= 1.24;*/
140
141  /*  if '01SEP2018'd <= TestDate <= '31DEC2018'd then IF= 1.23;*/
142
143      drop i;
144
145  run;
146  %mend;
147  %Inflation (01JAN2016,31MAR2016,1.25);
NOTE: Line generated by the invoked macro "INFLATION".
1     data Charge_update;     set charge;      if '&Date1.'d <= TestDate <= '&Date2.'d then do;
                                                  ----------
                                                  77
ERROR 77-185: Invalid number conversion on '&Date1.'d.

1  ! data Charge_update;     set charge;      if '&Date1.'d <= TestDate <= '&Date2.'d then do;
                                                                            ----------
                                                                            77
1  ! Inflation_Factor= &IF.;     array vars ED_charge INP_charge ICU_charge;     array vars_
1  ! ED_charge_ INP_charge_ ICU_charge_;     do i=1 to dim(vars);
ERROR: Invalid date/time/datetime constant '&Date1.'d.
ERROR: Invalid date/time/datetime constant '&Date2.'d.

ERROR 77-185: Invalid number conversion on '&Date2.'d.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.CHARGE_UPDATE may be incomplete.  When this step was stopped there were 0
         observations and 9 variables.
WARNING: Data set WORK.CHARGE_UPDATE was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

[ballardw]

Macro variables do no resolve inside single quotes.

Macro variables do no resolve inside single quotes.

Macro variables do no resolve inside single quotes.

Repeat until it sticks:

   if '&Date1.'d <= TestDate <= '&Date2.'d then do;

[Kurt_Bremser]

Even if you get the macro to work: you will do a pass through your dataset for every single time range. My suggested code does only one pass for all time ranges.