BookmarkSubscribeRSS Feed
zachvac
Calcite | Level 5

I have a sas program that is dealing with sas datasets and oracle database tables and am attempting to write a few functions to change between the two. I also wrote an oracle_intnx for using the intnx function with an oracle date that changes it to sas date, calls intnx, then converts it back to an oracle date. So the problem is it's doing the conversions fine, but then when I test it the return string is getting truncated somewhere. Anyway my code:

----------------------------------------------------------------------------------------------------------------------------------------------------------

%let start_date='01-JAN-2014';

libname output "[path removed]";

proc fcmp outlib=output.funcs.funcs;

  function oracledate_to_sasdate(oracledate $);

    stringDate = substr(oracledate,2,2)||substr(oracledate,5,3)||substr(oracledate,9,4);

    return (input(stringDate,date9.));

  endsub;

  function sasdate_to_oracledate(sasdate) $;

    put sasdate;

    test="'"||put(sasdate,date11.)||"'";

    put test;

    return (test);

    *return("'"||put(sasdate,date11.)||"'");

  endsub;

  function oracle_intnx(interval $,oracledate $,num) $;

    sas_date=oracledate_to_sasdate(oracledate);

    put sas_date;

    new_date=intnx(interval,sas_date,num);

    put new_date;

    toReturn = sasdate_to_oracledate(new_date);

    put toReturn;

    return (toReturn);

    *return(sasdate_to_oracledate(new_date));

  endsub;

quit;

options cmplib=output.funcs;

data _null;

a=oracle_intnx("day","&start_date",20);

put a;

run;

endsas;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

put outputs:

19724

19744

19744

'21-JAN-2014'

'21-JAN-

'21-JAN-

The first 4 make sense, the 4th is the put test in sasdate_to_oracledate and is exactly what I want. But when this is returned to oracle_intnx and immediately put it shows up truncated. Can someone explain why this is? Thanks.

3 REPLIES 3
ballardw
Super User

Looks like a variation on a SAS text variable without a defined length defaulting to 8 characters

'21-JAN- is 8 characters in length.

I think the variable SAS_DATE in your Oracle_intnx is treated as character as the function sasdate_to_oracledate creates a string.

zachvac
Calcite | Level 5

oh ok solved it. Just put a length statement on toReturn before assigning the results of the conversion to it. Seems dumb that it would work like that but I guess I'll just need to create explicit variables with explicit lengths rather than doing stuff all implicitly. Thanks.

fredmayer23
Fluorite | Level 6

Because SAS is an interpretive 4GL language, it assumes the length of the largest return value is the present one.  I would bet the day value of 01 is trimmed and reduced to 1, which throws it off.  You could send it a day greater than 09 and it would most likely work.  AND, you have indeed found the solution: the LENGTH statement will 'type' the storage requirement of the variable. 

you might investigate the INPUT function and see if converting the incoming ORACLE date to a SAS date on the SELECT statement.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 1701 views
  • 1 like
  • 3 in conversation