SAS and Excel Comparing Values Between the Two

[JediApprentice]

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

[LinusH · Accepted solution]

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.