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
Thanks for any help with the conversion.
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.
Well the first part is to do with encoding setup:
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;
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 .).
That gets most likely resolved by setting the appropriate NLS_LANG parameter as documented here:
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
...up to you.
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;
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;
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"
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.
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 ...
format mydate eurdfdd10.;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.