topic Re: Need help for this format in SAS Programming

Need help for this format

Ahsan — Fri, 12 Oct 2018 21:05:26 GMT

Hello I need some help on this

I used this to see the time on this variable ADJDC_TM the variable has a data in there so I used

to_char(ADJDC_TM, 'YYYY-MM-DD HH:MI:SS AM') and I got the correct result in SQL Developer

if I like to see the same result in SAS how do I that. What format I have to use. All the help appreciated.

Re: Need help for this format

novinosrin — Fri, 12 Oct 2018 21:07:45 GMT

something like

put(ADJDC_TM, datetime20.);

for the datetime value?

Re: Need help for this format

Ahsan — Fri, 12 Oct 2018 21:23:00 GMT

I did try this and I am not getting right results meaning wrong data and time.

Re: Need help for this format

novinosrin — Fri, 12 Oct 2018 21:46:48 GMT

Have you explored and exhausted all datetime formats available in sas?

Re: Need help for this format

Reeza — Fri, 12 Oct 2018 22:21:56 GMT

Show an example of what you're starting off with and what you're trying to do please.

Re: Need help for this format

Ahsan — Fri, 12 Oct 2018 23:40:20 GMT

that's what I did in SQL developer I have attached the screen shot I wanna do same in SAS too.

Re: Need help for this format

Ahsan — Fri, 12 Oct 2018 23:43:10 GMT

SELECT t1.TRNSCT_CNTL_NBR,
to_char(ADJDC_TM, 'YYYY-MM-DD HH:MI:SS AM'),
t1.CLM_HDR_PD_DT,
t1.HDR_TYP_CD,
t1.HDR_STS_CD,
t1.ADJDC_DT,
t1.ADJDC_TM,
t1.CLM_HDR_INSRT_DT
FROM CLAIM_HEADER_CUR t1 WHERE t1.TRNSCT_CNTL_NBR IN
(
'abcdexusususu',
'abaskfkfdkfkfkfk',

ORDER BY t1.CLM_HDR_PD_DT;

I want to do same in SAS

Re: Need help for this format

Reeza — Fri, 12 Oct 2018 23:50:58 GMT

No screenshot. Please also include the recommended SAS code you said did not work.

Re: Need help for this format

ballardw — Fri, 12 Oct 2018 23:56:07 GMT

Incomplete code doesn't help.

A straight SAS Proc SQL would likely have thrown an error with

to_char(ADJDC_TM, 'YYYY-MM-DD HH:MI:SS AM'),

Show the SAS code you ran. Copy it from the LOG with all the messages involved and paste into a code box opened using the forum's {I} icon.

Re: Need help for this format

Tom — Sat, 13 Oct 2018 00:00:43 GMT

I am pretty sure that even SQL_DEVELOPER cannot run emojis. 🙂

Remember to use the Insert Code or Insert SAS Code icons to get a pop-up window to enter your example data/code.

Are you sure that your SAS variable has datetime values? If you try to format a date value (number of days) as if it was a datetime value (number of seconds) you will get a value early in the year 1960.

Re: Need help for this format

Ahsan — Sat, 13 Oct 2018 00:10:08 GMT

Here is the screen shot of sql developer and SAS variable is not a datetime format. Here is the SAS code as well

PROC SQL;

CREATE TABLE WORK.QUERY_FOR_CLAIM_HEADER_CUR_0003 AS

SELECT t1.TRNSCT_CNTL_NBR,

t1.CLM_HDR_PD_DT,

t1.HDR_TYP_CD,

t1.HDR_STS_CD,

t1.ADJDC_DT,

t1.ADJDC_TM,

t1.CLM_HDR_INSRT_DT,

/* Test */

(PUT(ADJDC_TM, DATETIME20.)) AS Test

FROM WORK.QUERY_FOR_CLAIM_HEADER_CUR t1;

QUIT;

Re: Need help for this format

Reeza — Sat, 13 Oct 2018 02:06:26 GMT

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_CLAIM_HEADER_CUR_0003 AS
SELECT t1.TRNSCT_CNTL_NBR,
 
t1.CLM_HDR_PD_DT,
t1.HDR_TYP_CD,
t1.HDR_STS_CD,
t1.ADJDC_DT,
t1.ADJDC_TM,
t1.CLM_HDR_INSRT_DT,
/* Test */
ADJDC_TM AS Test1,
ADJDC_TM AS Test2 format=datetime.,
ADJDC_TM AS Test3 format=date9.
FROM WORK.QUERY_FOR_CLAIM_HEADER_CUR t1;
QUIT;

You already have the data. What does this show up as?

I suspect one of the above is what you need.

Re: Need help for this format

Tom — Sat, 13 Oct 2018 03:09:11 GMT

Your photograph is showing ADJDC_TM as just a date value. But the TO_CHAR() function was able to find time values to add to the dates.

If your values in SAS are only DATE values and not DATETIME values then the method you used to move the data has excluded the time part, just like what ever tool you used to make the report that you posted the photo of.

How did you move the data from that system into SAS?

Re: Need help for this format

Ahsan — Sat, 13 Oct 2018 03:33:41 GMT

I used your method test2 give me the date and time but the date is wrong, Test3 give me the correct date but no time.

Re: Need help for this format

Ahsan — Sat, 13 Oct 2018 03:35:51 GMT

I didn't move any data. SAS EG is connected to Oracle database so how I see date and time in SQL developer I want to see the same in SAS.

Re: Need help for this format

Tom — Sat, 13 Oct 2018 03:36:16 GMT

@Ahsan wrote:

I used your method test2 give me the date and time but the date is wrong, Test3 give me the correct date but no time.

So you have proved that you have DATE values and not DATETIME values. If try to format a date (number of days) as if it was a datetime (number of seconds) then it will look like a value in early 1960.

How did you create the SAS dataset?

Re: Need help for this format

Tom — Sat, 13 Oct 2018 03:47:39 GMT

@Ahsan wrote:
I didn't move any data. SAS EG is connected to Oracle database so how I see date and time in SQL developer I want to see the same in SAS.

EG is a Windows program you are running on your PC to connect to a server running SAS, not Oracle. The only way it can get data from Oracle is if you used some SAS commands to connect your SAS session to your Oracle database.

So what did you run?

Did you create a SAS libref that points to a schema (database) in Oracle?

Did you use PROC SQL to connect to Oracle and write a passthrough query to pull some data from Oracle?

Did you push some buttons in EG? What type of buttons?

You probably need to either change how the data is defined in Oracle so that SAS can autodetect that you want the full datetime value for that variable. Or tell SAS your self how to treat that variable. Look at the DBSASTYPE= dataset option.

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

Re: Need help for this format

Ahsan — Sat, 13 Oct 2018 03:57:12 GMT

I just login in SAS EG and which connect to Oracle library.

Re: Need help for this format

Ahsan — Sat, 13 Oct 2018 05:15:55 GMT

I used SQL Pass through and use the same code which I used in SQL developer and got the desired result.

Re: Need help for this format

ChrisNZ — Sat, 13 Oct 2018 09:08:26 GMT

You can also use SQL pass-through in SAS and use the to_char() function if you want.

There are many papers on how to do this.