Need help for this format

[Ahsan]

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.

[novinosrin]

something like 

put(ADJDC_TM, datetime20.);

for the datetime value?

[Ahsan]

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

[novinosrin]

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

[Reeza]

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

[Ahsan]

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

[Ahsan]

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

[Reeza]

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

[ballardw]

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.

[Tom]

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.

[Ahsan]

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;

[Reeza]

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. 

[Ahsan]

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.

[Tom]

---

@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?

[Tom]

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?