Convert SAS code to SQL

[Swall]

Hi,

I have the sas code below and need some help converting it to sql.

select as_of_date,

sum(org_book_bal) as prod,

sum(cur_yield*org_book_bal)/sum(org_book_bal) as wtd_cur_yield,

sum(transfer_rate*org_book_bal)/sum(org_book_bal) as wtd_transfer_rate,

sum(liquidity_premium_rate*org_book_balance)/sum(org_book_bal) as wtd_liquidity_rate,

sum(option_cost_rate*org_book_balance)/sum(org_book_bal) as wtd_option_cost,

sum(all_in_tp_rate*org_book_balance)/sum(org_book_bal) as wtd_all_in_FTP_rate,

sum(cur_net_rate*org_book_balance)/sum(org_book_bal) as wtd_net_rate,

from ofsaa_atomic.fsi_d_mortgages

where

datepart(as_of_date)>mdy(1,1,2020) and month(datepart(issue_date))=month(datepart(as_of_date)) and year(datepart(issue_date))=year(datepart(as_of_date)) and data_source_cd=80

group by as_of_date;

I appreciate the help!

Thanks

[ballardw]

SAS Proc SQL code is ANSI standard SQL except likely for SAS specific functions, such as date related items.

So what are you wanting it mangled to? Did you mean Microsoft SQL Server? some other DBMS?

Which specific parts are you concerned with?

If it is the dates then you the actual DBMS is very important as how dates are stored and manipulated are one of the differences between many systems.

There are many dialects of SQL. Without knowing which one you want there isn't really much we can do to help. Even when you tell us which specific system, by name - SQL is generic- without knowing which parts you need changed and why there isn't much we can do. You have to tell us, we don't have any mind readers that I am aware of on this forum (though some of the guesses as to intent of a question gets close sometimes.)

[Swall]

It is written in SAS's SQL syntax and I don't know what parts of the query to take out to get it in SQL form.

[Reeza]

You don't seem to know SQL either then...it's SQL and other than the DATEPART() function should work in almost all other RDBMS systems.

[Tom]

That is SQL code.

[AMSAS]

As @ballardw @Reeza and @Tom stated this is SQL, yes the SAS/SQL dialect

Having said that you are probably having issues around the where clause as this contains SAS functions (datepart, mdy, etc.) and I suspect references columns/variables that are stored as SAS date or datetime values

datepart(as_of_date)>mdy(1,1,2020) and month(datepart(issue_date))=month(datepart(as_of_date)) and year(datepart(issue_date))=year(datepart(as_of_date)) and data_source_cd=80

[Swall]

The issue was the datepart section. I was able to fix it. Thanks for the help!

[Tom]

So most of that is standard SQL so no modifications will be made.

Although you might want to change your coding style to not hide the continuation characters (commas or AND) or terminal characters (semi-colon) at the end of the line where it is hard for humans scanning the code to see them.

In your case fixing this makes it obvious your statement has an extra comma.

select
  as_of_date
, sum(org_book_bal) as prod
, sum(cur_yield*org_book_bal)/sum(org_book_bal) as wtd_cur_yield
, sum(transfer_rate*org_book_bal)/sum(org_book_bal) as wtd_transfer_rate
, sum(liquidity_premium_rate*org_book_balance)/sum(org_book_bal) as wtd_liquidity_rate
, sum(option_cost_rate*org_book_balance)/sum(org_book_bal) as wtd_option_cost
, sum(all_in_tp_rate*org_book_balance)/sum(org_book_bal) as wtd_all_in_FTP_rate
, sum(cur_net_rate*org_book_balance)/sum(org_book_bal) as wtd_net_rate
,
from ofsaa_atomic.fsi_d_mortgages
where datepart(as_of_date)>mdy(1,1,2020)
  and month(datepart(issue_date))=month(datepart(as_of_date))
  and year(datepart(issue_date))=year(datepart(as_of_date))
  and data_source_cd=80
group by as_of_date
;

So you will just need to translate the WHERE clause to one that can make those tests.  It looks like you have two tests that are comparing timestamp values. 

The first is testing if the timestamp in AS_OF_DATE (why is the variable named DATE when it has DATETIME values and not DATE values?) is after a particular day.  (Note one thing to watch out for if your timestamp values actually have a time of day component is whether you want to include a value like 01JAN2020:11:00 in the set or not. The current test would exclude it as the date part alone is NOT greater than the first day of 2020.

The second is testing if ISSUE_DATE and AS_OF_DATE as in the same month.

So you just need to learn how those two types of tests can be done the database you want to use (every implementation of SQL has its own way of doing things).

[ChrisNZ]

While on the topic of human-compatible legibility, I also make a point to always

- line up as much of the code as possible (maybe too much, I accept that)

- use case to differentiate language words from user-defined words.

Imho, this is so much more readable, looks more professional and prevents errors. Win-win-win!

Something like:

select
  AS_OF_DATE
, sum(ORG_BOOK_BAL)                                              as PROD
, sum(CUR_YIELD             *ORG_BOOK_BAL    )/sum(ORG_BOOK_BAL) as WTD_CUR_YIELD
, sum(TRANSFER_RATE         *ORG_BOOK_BAL    )/sum(ORG_BOOK_BAL) as WTD_TRANSFER_RATE
, sum(LIQUIDITY_PREMIUM_RATE*ORG_BOOK_BALANCE)/sum(ORG_BOOK_BAL) as WTD_LIQUIDITY_RATE
, sum(OPTION_COST_RATE      *ORG_BOOK_BALANCE)/sum(ORG_BOOK_BAL) as WTD_OPTION_COST
, sum(ALL_IN_TP_RATE        *ORG_BOOK_BALANCE)/sum(ORG_BOOK_BAL) as WTD_ALL_IN_FTP_RATE
, sum(CUR_NET_RATE          *ORG_BOOK_BALANCE)/sum(ORG_BOOK_BAL) as WTD_NET_RATE
,
from OFSAA_ATOMIC.FSI_D_MORTGAGES
where datepart(AS_OF_DATE)        > mdy(1,1,2020)
  and month(datepart(ISSUE_DATE)) = month(datepart(AS_OF_DATE))
  and year (datepart(ISSUE_DATE)) = year (datepart(AS_OF_DATE))
  and DATA_SOURCE_CD              = 80
group by AS_OF_DATE
;

[PaigeMiller]

Computing weighted averages via this method in PROC SQL

sum(cur_yield*org_book_bal)/sum(org_book_bal)

fails (gives the wrong answer) if there are missing values in either cur_yield or org_book_bal.

PROC SUMMARY doesn't have this problem with missing values.