- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Try it and test it
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I don't need any more correct answers 😉