topic Re: Trying to get rid of cumbersome if logic in SAS Programming

Trying to get rid of cumbersome if logic

JediApprentice — Mon, 13 Feb 2017 20:59:41 GMT

I'm attempting to transform my if logic into something more concise. The sample I provide is much smaller than the real data:

data have;
  infile datalines;
  input list $ 1-3 id $ 5-7 price $ 9-10;
  datalines;
BAC BB8 4a
BAC 22C  
BAC DDD 5b
CBA     6c
CBA 22A 4b
CBA 10B 4b
CBA 11A 5f
DAC 2CC  
DAC 11C 2e
DAC     3e
;
run;


data want;
  set have;
  if list='BAC' and id='BB8' and price='4a' then code='BBB';
  if list='BAC' and id='22C' and price=' ' then code='CCC';
  if list='BAC' and id='DDD' and price='5b' then code='DDD';
  if list='CBA' and id=' ' and price='6c' then code='FFF';
  if list='CBA' and id='22A' and price='4b' then code='FFF';
  if list='CBA' and id='10B' and price='4b' then code='GGG';
  if list='CBA' and id='11A' and price='5f' then code='HHH';
  if list='DAC' and id='2CC' and price=' ' then code='III';
  if list='DAC' and id='11C' and price='2e' then code='BBB';
  if list='DAC' and id=' ' and price='3e' then code='BBB';
run;

So I use those if statements to create a new field. But I don't want to have tons of if statements for every variation - I'd rather automate it somehow. Is this possible?

Re: Trying to get rid of cumbersome if logic

Rick_SAS — Mon, 13 Feb 2017 21:05:58 GMT

Perhaps you are looking for the SELECT-WHEN statement. An explanation and examples are available in the article "The SELECT statement in the SAS DATA step."

Re: Trying to get rid of cumbersome if logic

Reeza — Mon, 13 Feb 2017 21:23:28 GMT

That depends. Do you have the logic for your mappings somewhere? It has to come from somewhere.

Creating a table you can join to as a lookup.

One refactoring that you should look into, is combining all conditions into one where the outcome is the same.

ie all conditions that map to BBB should have a single IF/THEN condition. In your example you have three different IF statements for this single value.

Re: Trying to get rid of cumbersome if logic

Reeza — Mon, 13 Feb 2017 21:23:30 GMT

That depends. Do you have the logic for your mappings somewhere? It has to come from somewhere.

Creating a table you can join to as a lookup.

One refactoring that you should look into, is combining all conditions into one where the outcome is the same.

ie all conditions that map to BBB should have a single IF/THEN condition. In your example you have three different IF statements for this single value.

Re: Trying to get rid of cumbersome if logic

mkeintz — Tue, 14 Feb 2017 03:06:13 GMT

You could make a dataset RECODES with all the combinatinos of list/id/price and resulting code. Then use it as a hash lookup table in a subsequent data set, as in:

data recodes;
  input (list id price code) ($char4.);
datalines;
BAC BB8 4a  BBB 
BAC 22C     CCC 
BAC DDD 5b  DDD 
CBA     6c  FFF 
CBA 22A 4b  FFF 
CBA 10B 4b  GGG 
CBA 11A 5f  HHH 
DAC 2CC     III 
DAC 11C 2e  BBB 
DAC     3e  BBB 
run;

data want (drop=rc);
  set have;
  if _n_=1 then do;
    if 0 then set recodes;
    declare hash h (dataset:'recodes');
      h.definekey('list','id','price');
      h.definedata('code');
      h.definedone();
  end;   /* Added after @LaurieF's note */
  rc=h.find();
  if rc^=0 then code=' ';
run;

The variable RC is set to zero with a successful lookup. If it's not a zero then that combinatino of list/id/price was not in the recodes dataset, so code should be set to missing.

Re: Trying to get rid of cumbersome if logic

JediApprentice — Mon, 13 Feb 2017 22:08:52 GMT

Yes, I do have a mapping document with all the codes that correspond with each line of logic.

Re: Trying to get rid of cumbersome if logic

Cynthia_sas — Mon, 13 Feb 2017 22:20:38 GMT

Hi:

And, in addition to the elegant HASH approach, a slightly different approach with a user-defined format for a lookup list is how I was taught to do it in the golden olden days before HASH. (If we already had a table with the values we needed for the format, then we used the CNTLIN technique with PROC FORMAT to make the list.)

cynthia

Re: Trying to get rid of cumbersome if logic

ChrisNZ — Mon, 13 Feb 2017 22:34:03 GMT

Or old-fashioned and simpler to code and understand, but probably much slower than a hash table:

data HAVE(drop=CODE)
     CODE(index=(A=(LIST ID PRICE )));
  input LIST $ 1-3 ID $ 5-7 PRICE $ 9-10 CODE $ 12-14;
  cards;
BAC BB8 4a BBB 
BAC 22C    CCC 
BAC DDD 5b DDD 
CBA     6c FFF 
CBA 22A 4b FFF 
CBA 10B 4b GGG 
CBA 11A 5f HHH 
DAC 2CC    III 
DAC 11C 2e BBB 
DAC     3e BBB 
run;

data WANT ;
  set HAVE;
  set CODE key=A;
  if _IORC_ = %sysrc(_dsenom) then _ERROR_=0;
run;

Re: Trying to get rid of cumbersome if logic

LaurieF — Tue, 14 Feb 2017 02:49:22 GMT

@mkeintz …with an 'end;' statement to terminate the 'if _n_ = 1 then do;', of course!

Re: Trying to get rid of cumbersome if logic

mkeintz — Tue, 14 Feb 2017 03:07:00 GMT

Thx. Done.

Re: Trying to get rid of cumbersome if logic

JediApprentice — Mon, 13 Mar 2017 18:37:10 GMT

@Reeza How would I do a join on a table for a lookup? - I have a table with all of the values and corresponding codes in it.