topic Re: Can someone sense check my code please in SAS Programming

Can someone sense check my code please

anonymous_user — Wed, 08 Jul 2015 08:44:05 GMT

Hi,

I have the following code but I can't seem to get it to work. I have a feeling the case statements are wrong but unsure what the issues are.

What im trying to do is upload 4 tabs from a CSV file and create a table within SAS. I then need a statement to bring back the variables and calculations.

%macro Import(path = ,outputdataset = , sheetname = );
proc import datafile = "&path" out = &outputdataset dbms=excel replace;
sheet = "&sheetname";
run;
%mend;
%import(path= 'Mortgage finance\a&tp\DM\CRDM Flow Model\CRDM Flow Model.xls'outputdataset = tab1 ,sheetname =BO data – Allocations)
%import(path= 'Mortgage finance\a&tp\DM\CRDM Flow Model\CRDM Flow Model.xls'outputdataset = tab2 ,sheetname =BO data – Completions)
%import(path= 'Mortgage finance\a&tp\DM\CRDM Flow Model\CRDM Flow Model.xls'outputdataset = tab3 ,sheetname =Hedging Assumptions 1)
%import(path= 'Mortgage finance\a&tp\DM\CRDM Flow Model\CRDM Flow Model.xls'outputdataset = tab4 ,sheetname =Hedging Assumptions 2)

Proc sql;
Create table work.steve as
select
Product_End_Date as Product_end_date,
Term as Term,
Launch_Code as launch_code,
Weekly_Movement_Allocs as Weekly_movement_Allocs
LTD_Pipeline as Pipeline_ytd,
LTD_Completions as Completions_ytd,
CATX(",", product_end_date, term, launchcode)as Date_Term_Prod,
Term_hedge_2 as Term_hedge_2 ,
Prepayment_rate_hedge_2 as Prepayment_rate_hedge_2,
Conversion_Rate_hedge as Conversion_Rate_hedge,
Key_hedge as Key_hedge,
Product_Term_comps as Product_Term_comps,

case when Date_Term_Prod = Key_hedge then Conversion_Rate_hedge else 0 end as Alco_pc,
Case when Product_Term_comps = Term_hedge_2 then Prepayment_rate_hedge_2 else 0 end as prepayment,
case when alco_pc = 0 then Pipeline_ytd, Pipeline_ytd*alco_pc end as pipeline_x_alco_pc,
sum(case when pipeline_x_alco_pc + Completions_ytd * prepayment) as LTD,
case when Product_end_date = 0 then sum(Weekly_movement_Allocs * prepayment) else sum(Weekly_movement_Allocs * Alco_pc * prepayment) as weekly_movement

From Hedging_Dataset;
quit;

proc sql;
create table work.steve2 as
select
Product_Term_comps,
Pipeline_ytd,
alco_pc,
pipeline_x_alco_pc,
Completions_ytd,
prepayment,
LTD,
weekly_movement

from
work.steve;
quit;

Re: Can someone sense check my code please

MadhuKorni — Wed, 08 Jul 2015 09:04:37 GMT

Put a comma before ouputdataset in the macro call

else try this

%import(path= %nrstr(Mortgage finance\a&tp\DM\CRDM Flow Model\CRDM Flow Model.xls),outputdataset = tab1 ,sheetname =%str(BO data – Allocations));

Re: Can someone sense check my code please

RW9 — Wed, 08 Jul 2015 09:09:04 GMT

Well, market rate is a bout 60euro an hour...

Some pointers. First your not using CSV, your using Excel file format. I will point out that this is not a good idea, Excel is not a data transfer format, it doesn't have strcuture like proper data. Anyways, however you get the data in, I could not see the tab datasets created by the macro used anywhere or combined? Secondly format the code in a readable way, use indents, consitent casing, split case statements over rows. E.g.

case when <condition> then <value>

when <condition> then <value>

else <value> end as <variable>

You use sums() in your code, but I don't see any grouping.

You are missing a space before the as in: CATX(",", product_end_date, term, launchcode)as Date_Term_Prod,

This code is illogical: sum(case when pipeline_x_alco_pc + Completions_ytd * prepayment) as LTD,

There is no else or end for the case, and why bother putting case statements in a sum()?

As for your question, " I have a feeling the case statements are wrong but unsure what the issues are.", in what way are they wrong, other than maybe some typos I can't guess what you trying to achieve. I assume that your code will be validated by a second person, who will have a spec available, so they would be able to check any logical issues.

Re: Can someone sense check my code please

anonymous_user — Wed, 08 Jul 2015 09:27:42 GMT

The issue im having is how to write the code correctly for the sums and case statements. Im trying to replicate what has been completed within excel and the formulas used. Excel was used as a test so we knew what we needed to do within SAS but im finding it tricky in completing the code.