Looking for any efficient way.

Discaboota — Mon, 26 Dec 2022 08:15:06 GMT

Hey guys, I have to put flags for customers according to their balance for a particular product code. There is categorization on the basis of months as well. As the months increase from M+0 To M+2, upto M+8. Here months M+0 is Current month, M+1 last month and so on.

Is there any other way to do this which does not take so much manual work?
Any suggestions would be appreciated!

DATA PRODWISE_AMB_CHECK;
SET BC_UC_ALL(WHERE=(PRODUCT_CODE NOT IN('20101','20102','20103')));
IF (PRODUCT_CODE IN (20210) AND CORE_URBAN IN ('Urban') AND AMB_OCT_M0<10000 AND MONTH='M+0') OR (PRODUCT_CODE IN (20210) AND CORE_URBAN IN ('Core') AND AMB_OCT_M0<5000 AND MONTH='M+0' ) THEN PROD_BAL=0;
IF (PRODUCT_CODE IN (20210) AND CORE_URBAN IN ('Urban') AND AMB_OCT_M0>=10000 AND MONTH='M+0') OR (PRODUCT_CODE IN (20210) AND CORE_URBAN IN ('Core') AND AMB_OCT_M0>=5000 AND MONTH='M+0' )  THEN PROD_BAL=10;
IF (PRODUCT_CODE IN (20241,20250) AND AMB_OCT_M0<50000 AND MONTH='M+0') THEN PROD_BAL=0;
IF (PRODUCT_CODE IN (20241,20250) AND AMB_OCT_M0>=50000 AND MONTH='M+0') THEN PROD_BAL=10;
IF (PRODUCT_CODE IN (20109,20110) AND AMB_OCT_M0<5000 AND MONTH='M+0') THEN PROD_BAL=0;
IF (PRODUCT_CODE IN (20109,20110) AND AMB_OCT_M0>=5000 AND MONTH='M+0') THEN PROD_BAL=10;
IF (PRODUCT_CODE IN (20249,20139,20140) AND AMB_OCT_M0<500000 AND MONTH='M+0') THEN PROD_BAL=0;
IF (PRODUCT_CODE IN (20249,20139,20140) AND AMB_OCT_M0>=500000 AND MONTH='M+0') THEN PROD_BAL=10;
IF PRODUCT_CODE IN (20135,20255,20154,20155) AND AMB_OCT_M0 <100000 AND MONTH='M+0' THEN PROD_BAL=0;
IF PRODUCT_CODE IN (20135,20255,20154,20155) AND AMB_OCT_M0 >=100000 AND MONTH='M+0' THEN PROD_BAL=10;
IF PRODUCT_CODE IN (20134,20121) AND AMB_OCT_M0 <25000 AND MONTH='M+0'  THEN PROD_BAL=0;
IF PRODUCT_CODE IN (20134,20121) AND AMB_OCT_M0 >=25000  AND MONTH='M+0' THEN PROD_BAL=10;
IF (PRODUCT_CODE IN (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) AND CORE_URBAN IN ('Urban') AND AMB_OCT_M0<10000 AND MONTH='M+0') 
OR (PRODUCT_CODE IN (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) AND CORE_URBAN IN ('Core') AND AMB_OCT_M0<5000 AND MONTH='M+0' ) THEN PROD_BAL=0;
IF (PRODUCT_CODE IN (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) AND CORE_URBAN IN ('Urban') AND AMB_OCT_M0>=10000 AND MONTH='M+0') 
OR (PRODUCT_CODE IN (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) AND CORE_URBAN IN ('Core') AND AMB_OCT_M0>=5000 AND MONTH='M+0' ) THEN PROD_BAL=10;

Re: Looking for any efficient way.

mkeintz — Wed, 28 Dec 2022 02:51:06 GMT

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.

topic Re: Looking for any efficient way. in SAS Viya

Looking for any efficient way.

Re: Looking for any efficient way.