DATA Step, Macro, Functions and more

IF/THEN/DO Can I make it more concise?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

IF/THEN/DO Can I make it more concise?

Here is my sample data:

 

data MACID;
  input 'GER Pharm'n $ Instate $ Code_MAC $;
datalines;
CVS . .
CVS . MAC01
CVS . MAC02
CVS . MACIVL
CVS . MACMAILHIM 
CVS . MACMEDCARE 
CVS . MACMEDICAI 
CVS MACCHPMEDD .
CVS MACCHPMEDD MACMEDCARE 
CVS MACHZ .
CVS MACHZ MAC01 
CVS MACHZ MAC02 
CVS MACHZ MACMEDCARE 
CVS MACJX .
CVS MACJX MAC01 
CVS MACJX MACMEDCARE 
CVS MACNC .
CVS MACNC MAC01 
MHA LTC .
MHA LTC MACMEDCARE
Omnicare . .
Omnicare . MAC01
Omnicare . MAC02
Omnicare . MACIVL
Omnicare . MACMEDCARE
Omnicare . MACMEDICAI
Omnicare LTC .
Omnicare LTC MACMEDCARE
Omnicare MACHZ .	
Omnicare MACHZ	MAC01
Omnicare MACHZ	MAC02
Omnicare MACJX .	
Omnicare MACJX	MAC01
Omnicare MACNC	MAC01
Other . .
Other . MAC01
Other . MAC02 
Other . MACIVL 
Other . MACMAILHIM 
Other . MACMEDCARE 
Other . MACMEDICAI
Other LTC .
Other LTC MACMEDCARE 
Other MACCHPCOMM MACMEDICAI 
Other MACCHPMEDD .
Other MACCHPMEDD MACMEDCARE 
Other MACHZ .
Other MACHZ MAC01 
Other MACHZ MAC02 
Other MACHZ MACMEDCARE 
Other MACHZ MACMEDICAI 
Other MACJX .
Other MACJX MAC01 
Other MACJX MACMEDCARE 
Other MACJX MACMEDICAI 
Other MACNC .
Other MACNC MAC01 
Other MACNC MACMEDICAI
Pharmerica . .
Pharmerica . MAC01
Pharmerica . MAC02
Pharmerica . MACIVL
Pharmerica . MACMEDCARE
Pharmerica . MACMEDICAI
Pharmerica LTC .
Pharmerica LTC MACMEDCARE
Pharmerica MACHZ .
Pharmerica MACHZ MAC02
Pharmerica MACJX .
Pharmerica MACJX MAC01
Pharmerica MACNC .
Pharmerica MACNC MAC01 
Walgreens . .
Walgreens . MAC01
Walgreens . MAC02 
Walgreens . MACIVL 
Walgreens . MACMAILHIM 
Walgreens . MACMEDCARE 
Walgreens . MACMEDICAI 
Walgreens MACCHPMEDD .
Walgreens MACCHPMEDD MACMEDCARE 
Walgreens MACHZ .
Walgreens MACHZ MAC01 
Walgreens MACHZ MAC02 
Walgreens MACHZ MACMEDCARE 
Walgreens MACJX .
Walgreens MACJX MAC01 
Walgreens MACJX MACMEDCARE 
Walgreens MACNC .
Walgreens MACNC MAC01
;

And here is the code I use to create a new variable, MAC_List:

 

data MACLISTID;
  set EGTASK.QUERY_FOR_DATA_WITH_MAC_GRO_0001;
  length MAC_List $ 10;
if 'GER Pharm'n='CVS' then do;
  if Instate='' then do;
    if Code_MAC='' then MAC_List='NONMAC';
	else if Code_MAC='MAC01' then MAC_List='MAC1001A1J';
	else if Code_MAC='MAC02' then MAC_List='MAC1001A2J';
	else if Code_MAC='MACIVL' then MAC_List='MAC1001A3J';
	else if Code_MAC='MACMAILHIM' then MAC_List='MACMAILHIM';
	else if Code_MAC='MACMEDCARE' then MAC_List='MAC1002C1J';
	else if Code_MAC='MACMEDICAI' then MAC_List='MAC1001M1J';
  end;
  if Instate='MACCHPMEDD' and Code_MAC in('','MACMEDCARE') then MAC_List='MAC5752C1J';
  if Instate='MACHZ' then do;
    if Code_MAC in('','MAC01','MAC02') then MAC_List='MAC2501A1J';
	else if Code_MAC='MACMEDCARE' then MAC_List='MAC2502C1J';
  end;
  if Instate='MACJX' then do;
    if Code_MAC in('','MAC01') then MAC_List='MAC2251A1J';
    else if Code_MAC='MACMEDCARE' then MAC_List='MAC2252C1J';
  end;
  if Instate='MACNC' and Code_MAC in('','MAC01') then MAC_List='MAC1001A1J';
end;
if 'GER Pharm'n='Omnicare' then do;
  if Instate='' then do;
    if Code_MAC='' then MAC_List='NONMAC';
	else if Code_MAC='MAC01' then MAC_List='MAC01';
	else if Code_MAC='MAC02' then MAC_List='MAC02';
	else if Code_MAC='MACIVL' then MAC_List='MACIVL';
	else if Code_MAC='MACMEDCARE' then MAC_List='MACMEDCARE';
	else if Code_MAC='MACMEDICAI' then MAC_List='MACMEDICAI';
  end;
  if Instate='LTC' and Code_MAC in('','MACMEDCARE') then MAC_List='MAC1002C1N';
  if Instate='MACHZ' and Code_MAC in('','MAC01','MAC02') then MAC_List='MACHZ';
  if Instate='MACJX' and Code_MAC in('','MAC01') then MAC_List='MACJX';
  if Instate='MACNC' and Code_MAC='MAC01' then MAC_List='MACNC';
end;
if 'GER Pharm'n='MHA' and Instate='LTC' and Code_MAC in('','MACMEDCARE') then MAC_List='MAC1002C1O';
if 'GER Pharm'n='Other' then do;
  if Instate='' then do;
    if Code_MAC='' then MAC_List='NONMAC';
	else if Code_MAC='MAC01' then MAC_List='MAC01';
	else if Code_MAC='MAC02' then MAC_List='MAC02';
	else if Code_MAC='MACIVL' then MAC_List='MACIVL';
	else if Code_MAC='MACMAILHIM' then MAC_List='MACMAILHIM';
	else if Code_MAC='MACMEDCARE' then MAC_List='MACMEDCARE';
	else if Code_MAC='MACMEDICAI' then MAC_List='MACMEDICAI';
  end;
  if Instate='LTC' and Code_MAC='' then MAC_List='MAC01';
  if Instate='LTC' and Code_MAC='MACMEDCARE' then MAC_List='MACMEDCARE';
  if Instate='MACCHPCOMM' and Code_MAC='MACMEDICAI' then MAC_List='MACCHPCOMM';
  if Instate='MACCHPMEDD' and Code_MAC in('','MACMEDCARE') then MAC_List='MACCHPMEDD';
  if Instate='MACHZ' and Code_MAC in('','MAC01','MAC02','MACMEDCARE','MACMEDCAI') then MAC_List='MACHZ';
  if Instate='MACJX' and Code_MAC in('','MAC01','MACMEDCARE','MACMEDICAI') then MAC_List='MACJX';
  if Instate='MACNC' and Code_MAC in('','MAC01','MACMEDICAI') then MAC_List='MACNC';
end;
if 'GER Pharm'n='Pharmerica' then do;
  if Instate='' then do;
    if Code_MAC='' then MAC_List='NONMAC';
	else if Code_MAC='MAC01' then MAC_List='MAC01';
	else if Code_MAC='MAC02' then MAC_List='MAC02';
	else if Code_MAC='MACIVL' then MAC_List='MACIVL';
	else if Code_MAC='MACMEDCARE' then MAC_List='MACMEDCARE';
	else if Code_MAC='MACMEDICAI' then MAC_List='MACMEDICAI';
  end;
  if Instate='LTC' and Code_MAC in('','MACMEDCARE') then MAC_List='MAC1002C1P';
  if Instate='MACHZ' and Code_MAC in('','MAC02') then MAC_List='MACHZ';
  if Instate='MACJX' and Code_MAC in('','MAC01') then MAC_List='MACJX';
  if Instate='MACNC' and Code_MAC in('','MAC01') then MAC_List='MACNC';
end;
if 'GER Pharm'n='Walgreens' then do;
  if Instate='' then do;
    if Code_MAC='' then MAC_List='NONMAC';
	else if Code_MAC in('MAC01','MACMAILHIM') then MAC_List='MAC1001A1K';
	else if Code_MAC='MAC02' then MAC_List='MAC1001A2K';
	else if Code_MAC='MACIVL' then MAC_List='MAC1001A3K';
	else if Code_MAC='MACMEDCARE' then MAC_List='MAC1002C1K';
	else if Code_MAC='MACMEDICAI' then MAC_List='MAC1001M1K';
  end;
  if Instate='MACCHPMEDD' and Code_MAC in('','MACMEDCARE') then MAC_List='MAC5752C1K';
  if Instate='MACHZ' and Code_MAC in('','MAC01','MAC02') then MAC_List='MAC2501A1K';
  if Instate='MACHZ' and Code_MAC='MACMEDCARE' then MAC_List='MAC2502C1K';
  if Instate='MACJX' and Code_MAC in('','MAC01') then MAC_List='MAC2251A1K';
  if Instate='MACJX' and Code_MAC='MACMEDCARE' then MAC_List='MAC2252C1K';
  if Instate='MACNC' and Code_MAC in('','MAC01') then MAC_List='MAC1001A1K';
end;
run;

Is there any way I can make this more efficient/concise? Instead of having all these if statements?


Accepted Solutions
Solution
‎09-15-2016 03:55 PM
Super User
Posts: 17,840

Re: IF/THEN/DO Can I make it more concise?

You appear to be categorizing your data based on three fields

Make those fields and the look up values into a table. Then rather than if/then you join on the three fields.

View solution in original post


All Replies
Regular Contributor
Posts: 159

Re: IF/THEN/DO Can I make it more concise?

[ Edited ]

If you use PROC SQL instead of a DATA step, you can use case-when statements, which are known as switch statements in other programming languages.

 

This paper explains them. See also the documentation. It will definitely look cleaner.

Frequent Contributor
Posts: 123

Re: IF/THEN/DO Can I make it more concise?

Wouldn't it still only be minutely less concise? 

 

Because this:

 

if 'GER Pharm'n='CVS' then do;
  if Instate='' then do;
    if Code_MAC='' then MAC_List='NONMAC';
	else if Code_MAC='MAC01' then MAC_List='MAC1001A1J';
	else if Code_MAC='MAC02' then MAC_List='MAC1001A2J';
	else if Code_MAC='MACIVL' then MAC_List='MAC1001A3J';
	else if Code_MAC='MACMAILHIM' then MAC_List='MACMAILHIM';
	else if Code_MAC='MACMEDCARE' then MAC_List='MAC1002C1J';
	else if Code_MAC='MACMEDICAI' then MAC_List='MAC1001M1J';
  end;
end;

turns into this:

 

CASE
  WHEN 'GER Pharm'n='CVS' and Instate='' and Code_MAC='' then 'NONMAC'
  WHEN 'GER Pharm'n='CVS' and Instate='' and Code_MAC='MAC01' then 'MAC1001A1J';
  WHEN 'GER Pharm'n='CVS' and Instate='' and Code_MAC='MAC02' then 'MAC1001A2J'
  WHEN 'GER Pharm'n='CVS' and Instate='' and Code_MAC='MAIVL' then 'MAC1001A3J'
  WHEN 'GER Pharm'n='CVS' and Instate='' and Code_MAC='MACMAILHIM' then 'MACMAILHIM'
  WHEN 'GER Pharm'n='CVS' and Instate='' and Code_MAC='MACMEDCARE' then 'MAC1002C1J'
  WHEN 'GER Pharm'n='CVS' and Instate='' and Code_MAC='MACMEDICAI' then 'MAC1001M1J'
END AS MAC_List

...Which is only 2 or 3 lines shorter. I don't care necessarily about it looking cleaner, I was just wondering if I could use arrays/loops or something else...

Super User
Posts: 10,500

Re: IF/THEN/DO Can I make it more concise?

One approach might be a series of custom formats.

Example:

proc format library=work ;
value $Instate_CVS_MAC
   ' ' ='NONMAC'
   'MAC01' ='MAC1001A1J'
   'MAC02' ='MAC1001A2J'
   'MACIVL' ='MAC1001A3J'
   'MACMAILHIM' ='MACMAILHIM'
   'MACMEDCARE' ='MAC1002C1J'
   'MACMEDICAI' ='MAC1001M1J'
;
run;

Then this code snippet:

 

if 'GER Pharm'n='CVS' then do;
  if Instate='' then do;
    if Code_MAC='' then MAC_List='NONMAC';
	else if Code_MAC='MAC01' then MAC_List='MAC1001A1J';
	else if Code_MAC='MAC02' then MAC_List='MAC1001A2J';
	else if Code_MAC='MACIVL' then MAC_List='MAC1001A3J';
	else if Code_MAC='MACMAILHIM' then MAC_List='MACMAILHIM';
	else if Code_MAC='MACMEDCARE' then MAC_List='MAC1002C1J';
	else if Code_MAC='MACMEDICAI' then MAC_List='MAC1001M1J';
  end;

could be replaced with

 

if 'GER Pharm'n='CVS' then do;
  if Instate='' then MAC_List= Put(Code_mac,$Instate_CVS_mac.);

Another approach would be to have a data set with all of the values for the 'GER Pharm'n  Instate and Code_MAC variable plus the resulting Mac_List and then use either and SQL Join or Hash Table look up. If there is much volatility in the values involved one of the lookups may be more flexible in the long run.

 

 

If practical I would suggest NOT using a missing value in comparisons and have an actual value such as "NA" for Code_Mac.

Super User
Posts: 17,840

Re: IF/THEN/DO Can I make it more concise?

These are essentially lookups. 

Would it be possible to create a lookup table and merge the data in instead of doing if/then/else statements?

Frequent Contributor
Posts: 123

Re: IF/THEN/DO Can I make it more concise?

I'm assuming a lookup would work. I've never worked with hash tables before though, could you give me some general guidance as to how to start?

Super User
Posts: 17,840

Re: IF/THEN/DO Can I make it more concise?

I don't know hash tables, I was thinking a plain ol' data step or sql merge. 

You probably could do a hash table as well, but would still need the data in a table first.

Frequent Contributor
Posts: 123

Re: IF/THEN/DO Can I make it more concise?

Ah okay, I'd like to do accomplish it with a SQL join. How would I go about this? I know how to do a join (that's the simple part) but I don't know how to incorporate all of the logic.

Solution
‎09-15-2016 03:55 PM
Super User
Posts: 17,840

Re: IF/THEN/DO Can I make it more concise?

You appear to be categorizing your data based on three fields

Make those fields and the look up values into a table. Then rather than if/then you join on the three fields.

Frequent Contributor
Posts: 123

Re: IF/THEN/DO Can I make it more concise?

EGTASK.MACLISTID contains the lookup table with the 3 variables and values as well as the MAC Lists that are supposed to be assigned to each. So would I do something like this: (I'm not sure how to assign the MAC Lists).

 

PROC SQL;
  CREATE TABLE MACLISTS AS
  SELECT *
  FROM EGTASK.Data_With_Mac_Grouping t1 
       LEFT JOIN EGTASK.MACLISTID t2 (ON t1.'GER Pharm'n=t2.'GER Pharm'n 
       AND t1.Instate=t2.Instate AND t1.Code_MAC=t2.Code_MAC);
QUIT;
Regular Contributor
Posts: 159

Re: IF/THEN/DO Can I make it more concise?

[ Edited ]

A data step would work nicely:

 

data EGTASK.MACLISTID;
    input 'GER Pharm'n $ Instate $ Code_MAC $ MAC_List $;
    datalines;
CVS . . NONMAC
CVS . MAC01 MAC1001A1J
...
;

Also add t2.MAC_List to the SELECT clause.

 

...and for the record, you can use more descriptive aliases. I'd likely use d instead of t1 and maybe m in place of t2. Just so the letters match the original table names. Up to you.

 

Frequent Contributor
Posts: 123

Re: IF/THEN/DO Can I make it more concise?

What I did was - I put the lookup table in excel and then imported it (I'm in SASEG). So I have that dataset called MACLISTID with GER Pharm, Instate, Code_MAC, and MAC_List.

 


Then I try a join (with your suggestions): 

 

PROC SQL;
  CREATE TABLE MACLISTS AS
  SELECT *,
         M.MAC_List
  FROM EGTASK.Data_With_Mac_Grouping D 
       LEFT JOIN EGTASK.MACLISTID M (ON D.'GER Pharm'n=M.'GER Pharm'n 
       AND D.Instate=M.Instate AND D.Code_MAC=M.Code_MAC);
QUIT;

Is this going to work? Is a left join the proper type of join?

Super User
Posts: 17,840

Re: IF/THEN/DO Can I make it more concise?

Try it and test it Smiley Happy

Frequent Contributor
Posts: 123

Re: IF/THEN/DO Can I make it more concise?

It worked! Wow I didn't know it would be this simple. The final code is this:

 

PROC SQL;
  CREATE TABLE MACLISTS AS
  SELECT *,
         M.MAC_List
  FROM EGTASK.DATA_WITH_MAC_GROUPING2 D 
       LEFT JOIN EGTASK.MACLISTID M ON (D.'GER Pharm'n=M.'GER Pharm'n 
       AND D.Instate=M.Instate AND D.Code_MAC=M.Code_MAC);
QUIT;

Thanks to @paulkaefer and @Reeza. Wish I could accept both of your answers.

Super User
Posts: 17,840

Re: IF/THEN/DO Can I make it more concise?

I don't need any more correct answers Smiley Wink

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 645 views
  • 7 likes
  • 4 in conversation