I'm attempting to create two new fields in SAS based on info in an Excel sheet:
Within the SAS data, whenever the SERVICEPROVIDERID (in SAS) equals the NCPDP (in Excel) do the following:
1) Create new AWP Rate field:
(CASE
WHEN t1.CHannel = 'Retail' and t1.Brand_Generic = 'Brand' and t1.MAC_Flag= 'AWP' then (bring in brand rate field from excel)
WHEN t1.CHannel = 'Retail' and t1.Brand_Generic = 'Generic' and t1.MAC_Flag= 'NON-MAC' then (bring in generic rate field from excel)
END) AS New_AWP_Rate
2) Create new Dispensing Fee field:
(CASE
WHEN t1.CHannel = 'Retail' and t1.Brand_Generic = 'Brand' and t1.MAC_Flag= 'AWP' then (bring in brand dispensing fee field from excel)
WHEN t1.CHannel = 'Retail' and t1.Brand_Generic = 'Generic' and t1.MAC_Flag= 'NON-MAC' then (bring in generic dispensing fee field from excel)
END) AS NewDispensingFee
The bolded sentences are ones I don't understand how to implement in SAS. I am using SAS EG so I am able to import the excel sheet using the wizard and get it in to a SAS data set.
Thanks,
JediApprentice
Since you don't show the structure of the Excel file(s), it's hard to give exact advice-
Either way, you need to get the Excel data accessible from SAS, with a Libname Excel, or proc import, to name two ways.
Then in your SQL, you could probably do some kind of correlated subquery.
But that code is a bit awkward, so I think if your Excel data have right attributes, a join operation would be a simpler task.
Since you don't show the structure of the Excel file(s), it's hard to give exact advice-
Either way, you need to get the Excel data accessible from SAS, with a Libname Excel, or proc import, to name two ways.
Then in your SQL, you could probably do some kind of correlated subquery.
But that code is a bit awkward, so I think if your Excel data have right attributes, a join operation would be a simpler task.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.