using Put() to convert format doesn't work

[dxtran]

Hello

I tried to use the put() function (as suggested in this forum) to convert a # to text (line 40) and I get an error.  Help please.

29 proc sql ;
30 connect to odbc(dsn=AMP_DSN uid=Dyyyyyy password = XXXXXXXX!);

31
32
33 /* Get Network profile and export results as csv */
34
35 create table tbl_NetworkProfile_201612 as
36 select * from connection to odbc(
37
38 select distinct
39 a.year_month,
40 put(a.branch_id, 15.) as branch_id,
41 a.hh_id,
42 a.customer_segment as Segment,
43 b.cd_accts as numCD,
44 b.chk_accts as numChk,
45 b.heq_accts as numHE,
46 b.credit_card_accts as numCreditCard,
47 b.investment_accts as numInvestments,
48 b.ira_accts as numIRA,
49 b.mtg_accts as numMortgage,
50 b.mma_accts as numMMA,
51 b.sav_accts as numSAV,
2 The SAS System 12:27 Monday, January 13, 2020

52 c.deposit_avg_bal as depbal,
53 c.loan_curr_bal as loanbal
54 from
55 level2.groupings a, level2.accounts b, level2.balance c
56 where a.year_month ='201612'
57 and a.customer_segment <> 'unknown'
58 and a.year_month = b.year_month
59 and a.year_month = c.year_month
60 and a.hh_id = b.hh_id
61 and a.hh_id = c.hh_id
62 );
ERROR: CLI describe error: [Oracle][ODBC][Ora]ORA-00904: "PUT": invalid identifier : [Oracle][ODBC]Invalid descriptor index <1>.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

[Tom · Accepted solution]

That is an error from ORACLE, since you asked Oracle to use the PUT() function.  It says that Oracle does not have such a function.

I think in Oracle you would probably use the TO_CHAR() function, or some other method.

[dxtran]

Thank you.  That worked.  I saw the Oracle reference, but I didn't realize there was a difference.

[ed_sas_member]

Hi @dxtran 

You should replace

put(a.branch_id, 15.) as branch_id,

by 

a.branch_id format=15.,

in your PROC SQL.

Otherwise, you can also use PROC FEDSQL: https://documentation.sas.com/?docsetId=proc&docsetTarget=p0ahxz9t4dkcucn1uiqa42nbazbh.htm&docsetVer...

It will allow you not to adapt your SQL native code (e.g. use of the PUT() function to apply a format, etc.).

You will just need to execute a LIBNAME statement with the ORACLE engine before this procedure.

[dxtran]

Thank you!

[Patrick]

As @Tom writes your code uses explicit pass-through SQL so you need to use database function to_char() for such a conversion.

There is nothing in your SQL which SAS couldn't push to the database for execution (except the put() function) so another alternative would be to use implicit pass-through SQL as in the sample code below. The options used below will write to the SAS log which part of the SQL have been pushed to the database for execution.

options sastrace=',,,d' sastraceloc=saslog nostsuffix;
libname level2 odbc dsn=AMP_DSN uid='Dyyyyyy' password = 'XXXXXXXX!';
proc sql;
  /* Get Network profile and export results as csv */
  create table tbl_NetworkProfile_201612 as
    select distinct
      a.year_month,
      put(a.branch_id, 15.) as branch_id,
      a.hh_id,
      a.customer_segment as Segment,
      b.cd_accts as numCD,
      b.chk_accts as numChk,
      b.heq_accts as numHE,
      b.credit_card_accts as numCreditCard,
      b.investment_accts as numInvestments,
      b.ira_accts as numIRA,
      b.mtg_accts as numMortgage,
      b.mma_accts as numMMA,
      b.sav_accts as numSAV,
      c.deposit_avg_bal as depbal,
      c.loan_curr_bal as loanbal
    from
      level2.groupings a, level2.accounts b, level2.balance c
    where a.year_month ='201612'
      and a.customer_segment <> 'unknown'
      and a.year_month = b.year_month
      and a.year_month = c.year_month
      and a.hh_id = b.hh_id
      and a.hh_id = c.hh_id
  ;
quit;

Also: In case you have also SAS/Access to Oracle licensed, use this engine over ODBC.

[dxtran]

Thank you!  This is a bit (may be quite a bit) above my level of understanding right now, but I will keep it as reference.