BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

15 REPLIES 15
paulkaefer
Lapis Lazuli | Level 10

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.

JediApprentice
Pyrite | Level 9

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...

ballardw
Super User

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.

Reeza
Super User

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?

JediApprentice
Pyrite | Level 9

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?

Reeza
Super User

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.

JediApprentice
Pyrite | Level 9

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.

Reeza
Super User

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.

JediApprentice
Pyrite | Level 9

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;
paulkaefer
Lapis Lazuli | Level 10

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.

 

JediApprentice
Pyrite | Level 9

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?

Reeza
Super User

Try it and test it Smiley Happy

JediApprentice
Pyrite | Level 9

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.

Reeza
Super User

I don't need any more correct answers 😉

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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