BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hk186002
Calcite | Level 5

 

 

Hi All,

I have an urgent requirment where in I have to convert the below oracle query to SAS. I am begnner in SAS, I tried
to paste the below query in PROC SQL under programme block and ran the query but seems that
we ahve few functions which needs to be converted. I am finding challenge in converting the last two lines of the code. Can anyone pls
help me here.



SELECT to_number(OLS_DYN_APPLICATIONS.APPLICATION_ID), OLS_VW_MIN_STATUS_DATE.MIN_DATE_OF_EFFECT, trunc(OLS_VW_APP_SUBMITTED_DATE.MIN_DATE_OF_EFFECT), OLS_DYN_APPLICATIONS.CREATOR_ID, ST_STOP_APP_RSN_TYPES_HIST.DESCRIPTION, ( ORIGINATORS.FAMILY_NAME ) || ', ' ||( ORIGINATORS.FIRST_NAME ) || DECODE(( ORIGINATORS.MIDDLE_NAMES ),NULL,NULL,' ' || ( ORIGINATORS.MIDDLE_NAMES )), ORIGINATOR_ORG_UNITS.COST_CENTRE, OLS_DYN_APPLICATION_PRODUCTS.PROMO_CODE FROM OLS_ST_PRODUCT_STATUSES HISTORY_STATUSES, OLS_DYN_SYSTEM_USERS ORIGINATORS, OLS_DYN_ORGANISATIONAL_UNITS ORIGINATING_ORG_UNITS, OLS_DYN_LEGAL_ENTITIES, OLS_ST_SALES_CHANNEL ORIGINATOR_ST_SALES_CHANNEL, OLS_DYN_LOAN_APPLICATIONS, OLS_DYN_STOPPED_APPLICATIONS DYN_STOPPED_APPS_HIST, OLS_ST_STOP_APP_REASON_TYPES ST_STOP_APP_RSN_TYPES_HIST, OLS_DYN_ORGANISATIONAL_UNITS ORIGINATOR_ORG_UNITS, OLS_DYN_STOPPED_APP_REASONS DYN_STOPPED_APP_RSNS_HIST WHERE ( OLS_DYN_APPLICATIONS.APPLICATION_ID=OLS_DYN_LOAN_APPLICATIONS.APPLICATION_ID ) AND ( OLS_DYN_APPLICATIONS.APPLICATION_ID=OLS_DYN_LEGAL_ENTITIES.APPLICATION_ID ) AND ( OLS_DYN_LOAN_APPLICATIONS.APPLICATION_ID=OLS_DYN_APPLICATION_PRODUCTS.APPLICATION_ID ) AND ( ORIGINATORS.ORGANISATIONAL_UNIT_ID=ORIGINATOR_ORG_UNITS.ORGANISATIONAL_UNIT_ID ) AND ( OLS_DYN_APPLICATIONS.ORIGINATOR_ID=ORIGINATORS.SYSTEM_USER_ID ) AND ( ( ( ORIGINATOR_ORG_UNITS.ORGANISATIONAL_UNIT_NAME ) ne 'OLS Project Team' And ( ORIGINATOR_ORG_UNITS.ORGANISATIONAL_UNIT_NAME ) ne 'Mortgages - OLS/HOLO Support Desk' ) AND ( OLS_DYN_LEGAL_ENTITIES.LEGAL_ENTITY_ID = (select min(DLE2.LEGAL_ENTITY_ID) from OLS_DYN_LEGAL_ENTITIES DLE2 where DLE2.APPLICATION_ID = OLS_DYN_APPLICATIONS.APPLICATION_ID) ) AND ORIGINATOR_ORG_UNITS.COST_CENTRE NOT LIKE '306:143' ) AND OLS_VW_PRODUCT_DETAILS.PRODUCT_NAME NOT IN ('Indicative Reverse Mortgage', 'Indicative Housing Loan', 'Indicative Equity Line') AND (trunc(OLS_DYN_APP_PROD_STATUSES.DATE_OF_EFFECT) BETWEEN trunc(sysdate-30) AND trunc(sysdate-1) OR trunc(OLS_DYN_APPLICATIONS.PRE_TO_FULL_APPROVAL_DATE) BETWEEN trunc(sysdate-30) AND trunc(sysdate-1)) )

thanks for taking your time.
Regards,
krishna

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

Why to you need to convert it? If you wrap it in an a SAS SQL PASSTHRU query you don't have to change any of your code:

 

proc sql;
  connect to oracle(--- put your Oracle connection stuff here ----);
  create table want as select * 
  from connection to oracle(

---- put your Oracle query here -----

  );
  disconnect from oracle;
quit;

 

View solution in original post

8 REPLIES 8
hk186002
Calcite | Level 5

I tried to write the decode funtion as
select family_name||','||first_name|| case when MIDDLE_NAMES is null then ' ' else ' '|| MIDDLE_NAMES end,

 

 

and for to_number(OLS_DYN_APPLICATIONS.APPLICATION_ID) ----------to informat OLS_DYN_APPLICATIONS.APPLICATION_ID $2;

 

not sure if I am right here

SASKiwi
PROC Star

Why to you need to convert it? If you wrap it in an a SAS SQL PASSTHRU query you don't have to change any of your code:

 

proc sql;
  connect to oracle(--- put your Oracle connection stuff here ----);
  create table want as select * 
  from connection to oracle(

---- put your Oracle query here -----

  );
  disconnect from oracle;
quit;

 

hk186002
Calcite | Level 5

Hi 

 

 

SASKiwi
PROC Star

You aren't getting the usual SAS log messages at the end of your SQL step. That suggests an unbalanced quote, bracket or something similar.

 

Copy the Oracle code back into your Oracle query tool (SQL Plus?) and run it there - does it run correctly? If not correct it until it does then paste it back into your SAS program.

 

Patrick
Opal | Level 21

@hk186002

I believe you're missing a closing bracket.

 

The Oracle SQL you've posted ends with a closing bracket:

  OR   trunc(OLS_DYN_APPLICATIONS.PRE_TO_FULL_APPROVAL_DATE)  BETWEEN  trunc(sysdate-30) AND trunc(sysdate-1))
  )

When you wrap this into a SAS SQL pass-through block then it should look like what @SASKiwi posted.

proc sql;
  connect to oracle(--- put your Oracle connection stuff here ----);
  create table want as select * 
  from connection to oracle(

---- put your Oracle query here -----

  );
  disconnect from oracle;
quit;

Looking into the log you've posted, the closing bracket for the from connection to oracle (....) is missing.

Line 104 in the log you've posted needs another closing bracket. I'd expect your code to look like:

103          OR   trunc(OLS_DYN_APPLICATIONS.PRE_TO_FULL_APPROVAL_DATE)  BETWEEN  trunc(sysdate-30) AND trunc(sysdate-1))
104           )
          );
hk186002
Calcite | Level 5

Hi All,

 

thank you very much for the help...the passthrough worked and was the issue with brackets.....thanks gurus...bit pressure releaved...now its time to learn some SAS stuff ....thanks again...

SASKiwi
PROC Star

Great to hear. In that case please mark your post as answered.

hk186002
Calcite | Level 5
Thank you Patrick...it worked:)

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2126 views
  • 2 likes
  • 3 in conversation