Your code shows two types of conditions for month='M+0':
Simple cutoff: For given product codes, there are specific cutoff values for AMB_OCT_M0. In every case when AMB_OCT_M0 is below the cutoff then PROD_BAL=0, otherwise PROD_BAL=10.
core_urban cutoff. For given product code in combination with core_urban='Urban', or 'Core', there are specific cutoff values. Again, in every case when AMB_OCT_M0 is below the cutoff then PROD_BAL=0, otherwise PROD_BAL=10.
You can make a dataset for each of these cutoff structures. Then use those datasets as hash objects, for quick lookup of product code, month, (and core_urban) to retrieve the cutoff value, followed by testing AMB_OCT_MO against that cutoff.
Here's what the code would look like:
DATA PRODWISE_AMB_CHECK (drop=_:);
SET BC_UC_ALL(WHERE=(PRODUCT_CODE NOT IN('20101','20102','20103')));
if _n=1 then do;
if 0 then set simple_cutoff core_urban_cutoff;
declare hash s (dataset:'simple_cutoff');
s.definekey('month','product_code');
s.definedata(all:'Y');
s.definedone();
declare hash cu (dataset:'core_urban_cutoff');
cu.definekey('month','core_urban','product_code');
cu.definedata(all:'Y');
cu.definedone();
end;
if s.find()=0 then prod_bal=ifn(amb_oct_m0<_cutoff,_bal_below_cutoff,_bal_not_below_cutoff); else
if cu.find()=0 then prod_bal=ifn(amb_oct_m0<_cutoff,_bal_below_cutoff,_bal_not_below_cutoff);
run;
This code must be preceded by creating the two datasets: SIMPLE_CUTOFF and CORE_URBAN_CUTOFF, as here (based on your rules):
data simple_cutoff;
month='M+0'; _bal_below_cutoff=0; _bal_not_below_cutoff=10;
do product_code=20241,20250; _cutoff=50000; output; end;
do product_code=20109,20110; _cutoff=5000; output; end;
do product_code=20249,20139,20140; _cutoff=500000; output; end;
do PRODUCT_CODE=20135,20255,20154,20155; _cutoff=100000; output; end;
do PRODUCT_CODE =20134,20121; _cutoff=25000; output; end;
run;
data core_urban_cutoff;
month='M+0'; _bal_below_cutoff=0; _bal_not_below_cutoff=10;
do PRODUCT_CODE=20210;
CORE_URBAN='Urban'; _cutoff=10000; output;
CORE_URBAN='Core'; _cutoff=5000; output;
end;
do PRODUCT_CODE=20204,20201,20202,20203,20205,20206,20207,20211,20212,20213,20214,20215,20216,20217,20218,20219,20220,20221,20222,20223,20224,20225,20226,20227,20228,20229;
CORE_URBAN='Urban'; _cutoff=10000; output;
CORE_URBAN='Core'; _cutoff=05000; output;
end;
run;
The hash methods s.find() and cu.find() return zeroes when the lookup is successful, i.e. matching values of product_code and month (and sometimes core_urban) are found. As part of a "successful" find method, the data values for _cutoff, _bal_below_cutoff, and _bal_not_below_cutoff are retrieved.
If you have simple cutoff for other months, just modify creation of the simple_cutoff dataset. Similarly for other months that need the core_urban value, just modify creation of the core_urban_cutoff dataset.
Given the absence of sample data, the code above is untested.
Also, I suggest you print out the cutoff datasets to make sure you've created the intended rules.
... View more