PROJECT 4 - MACROS AND OPIODS
OVERVIEW
At this stage in the game, we should be comfortable with PROC IMPORT, EXPORT, SORT, MEANS, basic DATA step, SGPLOT and some SQL. This week we’ll dig in and make sure we are comfortable with these core procedures of SAS. We’ll also follow our framework Stage, Structure, Cleanse, Transform and Explore as we analyze our opioid data. Finally, we’ll dig a little deeper into PROC MEANS and FREQ and learn how we can automate common analysis with SAS macro.
TASKS
TASK 1 – STAGE
Import the following CSV files into SAS using PROC IMPORT – yes just like PROJECT 3!
• NC_Physician.csv
• NC_Physician_Education.csv
• NC_Physician_Prescriptions.csv
• NC_Zip.csv
• OPIOD_LIST.CSV
TASK 2 – STRUCTURE
TASK 2A – PHYSICIAN MASTER
Create a PHYSICIAN_MASTER table and create a couple of new variables. To do this you’ll want to join Physician to Physician Education on NPI and to NC Zip on zip_code;
1. Take all of the fields from NC_PHYSICIAN and INNER JOIN this to NC_Physician_Education on NPI number, don’t worry about the field overlap.
2. join to NC_ZIP buy you’ll notice that NC_SIP has 5 digit Zipcodes while NC_physician has both 5 and 9 digit Zips. You’ll need to resolve this in order to join correctly! To do this you need to use PUT to convert numbers to character, in this case 9. Or a 9 character, character field. You’ll use LEFT to left justify the zip characters. Finally, you’ll use substring to chop the first 5 digits of a zip code. LIKE THIS:
SUBSTR(left(PUT(zip_code,9.)),1,5);
• Also, note you must join by LIKE Data types so IF NC_ZIP’s zipcode column is numeric guess what we’ll need to convert that too. What does that? PUT converts numeric to character! You tell it a format to PUT it to. In this case 5.
o put(B.ZIP_CODE,5.);
3. Next create a couple of useful variables.
New Fields Formula
Years_in_Practice 2018 – a.Graduation_Year Num
Full_Name compbl(a.first_name ||' '|| a.middle_initial ||' '|| a.last_name) Char
- Years_In_Practice like this:
2020 - Graduation_Year as years_in_practice,
Full name like this, COMPBL function removes multiple blanks in a character string by translating each occurrence of two or more consecutive blanks into a single blank.
- compbl(first_name ||' '|| middle_initial ||' '|| last_name) as full_name
My advice is to break it up into two or more steps (unless you are comfortable with SQL then just do it in one). All we are doing is joining three tables together.
TASK 2B – TOP OPIOID / NON-OPIOID PRESCRIPTIONS
PART 1. NOW YOU ARE GOING TO CREATE A N_SCRIPTS TABLE.
Your final N_SCRIPTS table will contain the Sum of Total Day Supply for the top 5 opioids and top 5 non-opioids. Hint: WHERE DRUG NAME IN ()
The list of opiate DRUG_NAMES:
● HYDROCODONE-ACETAMINOPHEN
● TRAMADOL HCL
● OXYCODONE-ACETAMINOPHEN (AKA Percocet)
● OXYCODONE HCL
● FENTANYL
The list of non-opiate DRUG_NAMES:
● LEVOTHYROXINE SODIUM
● POTASSIUM CHLORIDE
● METFORMIN HCL
● DILTIAZEM HCL
● GABAPENTIN
When we are done the Structure of your n_scripts table should look like this.
Column Desc Type
NPI national Provider ID – this is what we’ll join to Num
Drug_Name Drug Name Num
SUM_TOT_DAY_SUPPLY Sum of Total Day Supply Num
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
