conversion latin and date proc sql from oracle

[PhilipH]

Hi SAS Experts,

I am selecting with proc sql directly from the Oracle DB.
The data has letters like ü/ö/ä but in my SAS data-set those
are replaced with ?. How can I tell the proc sql to do the right conversion
without messing up the SQL?
The same applies to the date. The SAS Proc sql creates 24NOV2015:00:00:00 but I would
prefer 24.11.2015.

Thanks for any help with the conversion.

[Doc_Duke]

You should be able to address the font with one of the alternative fonts in SAS.  Search of "oracle sas fonts" for some help there.

Oracle does not recognize a date data type; it just has date-time and gets to dates by formatting.  If you want to store the date-time from Oracle as a date data type in SAS, you will need to do a data transformation with the DATEPART function and assign the perferred format.  If SAS doesn't supply the one you like, you can build your own using PROC FORMAT.

[RW9]

Well the first part is to do with encoding setup:

http://support.sas.com/documentation/cdl/en/nlsref/61893/HTML/default/viewer.htm#a002610945.htm

The second question, dates, that is normal showing for datetime.  If you want a different format then you need to format the date as you want, for instance;

date=datepart(datetimevar);

format date ddmmyy10.;

will show only date part and in ddmmyy10.  Off the top of my head I don't think it will show "." between each part though as that is very unsual.  Have a look at the formats built in or make your own with picture formats if needed (though I would use - rather than .).

[Patrick]

Question marks

That gets most likely resolved by setting the appropriate NLS_LANG parameter as documented here:

http://support.sas.com/kb/51/411.html 

Data Formats 

As others already mentioned an Oracle DATE column actually stores values as DateTime (precision down to the second) and a TIMESTAMP stores DateTime values with fractional seconds up to a precision of 6 decimals.

If reading such fields into SAS the correct conversion is to a SAS DateTime value - and SAS then applies a standard datetime format to these datetime values.

If it's just about display: Apply another format to the SAS variable and/or convert the SAS datetime value to a SAS data value (using datepart() )

Another option would be to use DBSASTYPE for selected columns

http://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#p1gbg4uuvac9p3n1hr...

...up to you.

[PhilipH]

Hi SAS Experts,

@RW9, I added the encoding to my data-set creation but this had no effect.
My current SAS encoding is LATIN9 which is ISO 8859-15 and should cover all
European languages (which suits my needs). So why is ü/ä/ö not correctly displayed?


@Doc_Duke, @ Patrick
Date conversion did not work either with TO_DATE. SAS still displays
07JUL2003 00:00:00 I am using SQL Pass-Through.

proc sql;
connect to oracle as oracleadb (user=xxx password='xxx' path=xxx);
create table sasdwhb.DMP_xxx (COMPRESS=YES ENCODING='UTF-8')
as
(
select * from connection to oracleadb
(
SELECT TO_DATE(T1.PD,'DD/MM/YY') AS PN_DAT

[Patrick]

For the encoding:

You need to set the NLS_LANG parameter as documented in the link I've posted already. The problem is highly likely not how SAS writes the data but how Oracle sends the data. The NLS_LANG parameter tells Oracle how to send them (I've been there and that was the solution).

Date conversion did not work either

TO_DATE converts a string to an Oracle Date (which is a DateTime value). It doesn't matter which Datetime format model you're using - the result is ALWAYS of data type DATE - and Oracle data type DATE stores values as "DateTime" down to the second.

If your format model is something  like DDMMYYYY without a time component then the DateTime value on the Oracle side is simply "beginning of the day"; and that's exactly what you then get on the SAS side.

You can now do one of two things on the SAS side

a) convert the SAS DateTime value to a Date value (using the DATEPART() function) and then assing a Date format to the newly created variable

b) apply another DateTime format to the numeric SAS variable containing a SAS DateTime value so that it only prints with the Date component as you want it. I couldn't find an OOTB format for what you want but you can always create a picture format which gives you exactly what you want.

Sample code for options a) and b)

data have;
  dttm='07JUL2003 00:00:00'dt;
  output;
  stop;
run;

proc format;
  picture my_dttm (default=10)
    other='%0d.%0m.%0Y' (datatype=datetime);
run;

proc sql;
  select 
    dttm as dttm1 format=datetime21.,
    dttm as dttm2 format=my_dttm.,
    datepart(dttm) as dt1 format=eurdfdd10.
  from have
  ;
quit;

[PhilipH]

Hi Patrick,

Thanks for your reply.
I am not the Oracle admin but the NLS_CHARACTERSET is WE8ISO8859P15 which is Latin-9, the same
as in SAS. The Oracle DB shows the correct values and no question marks.
I just wonder if our SAS UNIX machine misses the NLS_LANG environmental variable.
Have to check that with the SAS admin.

Secondly, about the date conversion.
I was hoping that the new string converted with TO_DATE and
passed to SAS would be formatted the right way.
I am using SQL-Pass-Through and datepart with fomrat = is
not valid Oracle Syntax (ORA-00923) and I dont like
the idea of going through my huge dataset again with standard
PROC SQL just to conver the date. It would be great to do it
in the SQL Pass-Through in one go with the selection.

What do you think? Is that possible?

[Patrick]

Yes, have the NLS_LANG setting on the SAS side checked. That's normally the culprit.

I've just checked what we had to modify in an actual project (UNIX):

File to modify:
/app/sas/sashome/SASFoundation/9.4/bin/sasenv_local
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

The NLS_LANG value is eventually different for your site. 

As for Oracle DATE. It's something I have seen quite a few people with SAS background being confused. Just try to get it into your head that in Oracle DATE is "the same" like DATETIME in SAS - and that there is no SAS DATE concept in Oracle.

The Oracle TO_DATE() function converts a string to an Oracle DATE (like in SAS an input function with a datetime informat would). So the result is always an Oracle DATE (=SAS DATETIME).

You could convert an Oracle DATE to a string using TO_CHAR() and you would then get exactly this string on the SAS side in a SAS character variable.

There is nothing like DATEPART() in Oracle as in Oracle it's always a count of seconds and not of days. 

If you want to align an Oracle DATE to "begining of day" then you can use the TRUNC() function - that's like using intnx('dtday',mySASdttmVar,0,'b') within SAS for a SAS DateTime value.

Below a code example of how you could approach this - but be certain that the Oracle DATE value is always "beginning of the day" as else you're truncating values.

proc sql;
  connect to oracle as oracleadb (user=xxx password='xxx' path=xxx);
  create table sasdwhb.DMP_xxx (COMPRESS=YES) as
    select 
      datepart(my_ora_date_column) as my_ora_date_column format=eurdfdd10.,
      some_other_column
    from connection to oracleadb
      (
        SELECT 
          my_ora_date_column,
          some_other_column
        from oraschema.mytable
      )
  ;
  disconnect from oracleadb;
quit;

[PhilipH]

OK. Thanks. Still checking why we have export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 and why not to change it. I keep you up to date.

[PhilipH]

We talked to SAS support. They said that the problem is that our Oracle DB uses a different encoding.

[MicSun]

Hi Patrick,

I have same issue when query some special charactors from oracle DB, which encoding is AL32UTF8. So, I edit Windows "user variable" by creating NLS_LANG paramater. It works on PC SAS or SAS EG (didn't connect to UNIX server). 

Also, followed instruction to add NLS_LANG into UNIX .profile, UNIX SAS is fine but I have to send SAS code command under UNIX platform. 

However, SAS EG connected to UNIX server still have issue to execute and error message is "

ORA-29275 - partial multibyte character Enterprise Guide"

[Patrick]

@MicSun

Great that you first searched the communities for similar problems than yours to find answers.

Please don't post new questions into the middle of old discussions. Create a new question and then eventually reference the old discussion there.

[MicSun]

Thank you, Patrick for your reply. I have created a new posting for my question.

https://communities.sas.com/t5/SAS-Enterprise-Guide/NLS-LANG-setting-in-SAS-EG-working-in-UNIX-serve...

[Patrick]

BTW: When you push SAS data into Oracle then be aware that because SAS has no datatype of DATE but stores such values in a numeric variable, the only way that the SAS Access engine can determine that this numeric value needs to get converted to an Oracle DATE is the SAS format applied to the numeric variable.

I've made the experience that the SAS Access engine doesn't recognise all SAS Date and DateTime formats (for example ISO ones) and in such cases converts the number in SAS to NUMERIC in Oracle - which is not what you want.

To avoid such issues, I wouldn't use any other formats than Date. and Datetime for permanent assignment to numeric SAS variable containing a SAS Date or DateTime value.

You can always use another format like EURDFDD. for printing using code like

proc print ...

  var mydate;

  format mydate eurdfdd10.;

run;

[Satish_Parida]

The Link 

http://support.sas.com/kb/18/688.html

describes how to add the NLS_LANG in to windows and Linux system.

You can also get the list of values to put as per the requiremnt language.