Replicate Excel Formulas in SAS

Reply
Frequent Contributor
Posts: 142

Replicate Excel Formulas in SAS

Hi

I have a spreadsheet in excel which has 4 tabs of which hold the data. I have code to upload this into SAS so I have one table with all the data called 'DM'.

Now I need some SAS code to extract the data into another table called DM_FINAL and i need and replicate the formulas used within excel. On the left hightlighted in RED is what I need the headers to be labelled. On the right is the excel formula currently used.

Header                                        Formula

date , term, Product code             ='BO data - Allocations'!C3&'BO data - Allocations'!B3&RIGHT('BO data - Allocations'!F3,4)

pipeline(ytd)                                ='BO data - Allocations'!G3

alco%                                         =IFERROR(VLOOKUP(A2,'Hedging Assumptions 1'!A:F,6,FALSE),0)

pipelin* alco %                             =IF(C2=0,B2,B2*C2)

compleyions ytd                           ='BO data - Allocations'!H3

Prepayment                                 =VLOOKUP('BO data -completions'!B3,'hedging assumption 2'!A:B,2,FALSE)

LTD                                             =(D2+E2)*F2

Weekly Movement                        =IF(C2=0,'BO data - Allocations'!I3*'convert allocations data'!F2,'BO data - Allocations'!I3*'convert allocations data'!C2*'convert allocations data'!F2)

Does anyone know how to write this code up please? I appreciate it might not be the easiest but any help would be appreciated as it's more complex than my skillset.

Thanks

Super User
Posts: 5,380

Re: Replicate Excel Formulas in SAS

SAS and Excel are two quite different tools. The formulas can inspire you, and give you some kind information on business rules.

But you need to rethink how to actually implement your solution. So it's better first to actually understand what these formulas stand for, translate it to natural language. And then do your SAS programming.

If this is over your head, hire the muscle to do this, or try to get some appropriate training.

Data never sleeps
Frequent Contributor
Posts: 142

Re: Replicate Excel Formulas in SAS

To be fair some I can do. This is the direction I have been given to try and implement this hence needing some help. If this can be done it will be of huge benfit to me on my daily processes. At the moment it's very labour intensive. I just need some steer and I can maybe build on from it.

Ask a Question
Discussion stats
  • 2 replies
  • 191 views
  • 0 likes
  • 2 in conversation