SAS and Excel Comparing Values Between the Two

JediApprentice — Tue, 21 Jun 2016 17:53:01 GMT

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

Re: SAS and Excel Comparing Values Between the Two

LinusH — Tue, 21 Jun 2016 19:03:08 GMT

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.

topic Re: SAS and Excel Comparing Values Between the Two in SAS Programming

SAS and Excel Comparing Values Between the Two

Re: SAS and Excel Comparing Values Between the Two