DATA Step, Macro, Functions and more

SAS and Excel Comparing Values Between the Two

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

SAS and Excel Comparing Values Between the Two

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


Accepted Solutions
Solution
‎06-21-2016 06:23 PM
Super User
Posts: 5,256

Re: SAS and Excel Comparing Values Between the Two

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.

Data never sleeps

View solution in original post


All Replies
Solution
‎06-21-2016 06:23 PM
Super User
Posts: 5,256

Re: SAS and Excel Comparing Values Between the Two

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.

Data never sleeps
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 182 views
  • 0 likes
  • 2 in conversation