Help using Base SAS procedures

not able to resolve conversion errors

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

not able to resolve conversion errors

Hi All,

I am new to SAS and want to convert all my SQL queries to SAS compatible. And my first challenge is - I need to convert a timestamp value to character value.

SQl query -

rsubmit;
proc sql;
select distinct A.OFFRG_N,                  /*CHAR(12)*/

A.CRT_TS                                           /*CHAR(26)*/

from CSTST231_PMRINSD A, CCTRP101_OFF_TERM B
where A.OFFRG_N = B.OFFRG_N
and A.CRT_TS = B.OFFRG_TM_CRT_TS

except

select OFFRG_N,                               /*CHAR(12)*/
OFFRG_TM_CRT_TS                         /*TIMESTAMP*/
from CCTRP161_PMRINSD;
quit;

below is the log -

 

84 rsubmit;

NOTE: Remote submit to MYNODE commencing.

 

619 proc sql;

620 select distinct A.OFFRG_N,

621 A.CRT_TS

622 from CSTST231_PMRINSD A, CCTRP101_OFF_TERM B

623 where A.OFFRG_N = B.OFFRG_N

624 and A.CRT_TS = B.OFFRG_TM_CRT_TS

625

626 except

627

628 select OFFRG_N,

629 OFFRG_TM_CRT_TS

630 from CCTRP161_PMRINSD;

ERROR: Expression using equals (=) has components that are of different data types.

ERROR: Column 2 from the first contributor of EXCEPT is not the same type as its counterpart from

the second.

ERROR: Numeric expression requires a numeric format.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

631 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: Remote submit to MYNODE complete.


Accepted Solutions
Solution
‎08-08-2014 02:02 PM
Super Contributor
Posts: 578

Re: not able to resolve conversion errors

so the sas function would be put as in put(OFFRG_TM_CRT_TS, datetime20.).  You'd have to match the formatting of the CRT_TS.

This should work ok if the source datasets are both sas...but if you're using some rdbms, it will probably force SAS to generate all of the data locally which will be a performance hit.

You could optionally convert CRT_TS to datetime instead.

View solution in original post


All Replies
Solution
‎08-08-2014 02:02 PM
Super Contributor
Posts: 578

Re: not able to resolve conversion errors

so the sas function would be put as in put(OFFRG_TM_CRT_TS, datetime20.).  You'd have to match the formatting of the CRT_TS.

This should work ok if the source datasets are both sas...but if you're using some rdbms, it will probably force SAS to generate all of the data locally which will be a performance hit.

You could optionally convert CRT_TS to datetime instead.

Occasional Contributor
Posts: 13

Re: not able to resolve conversion errors

Thank you so much, It worked for me.

And now I am on the second challenge.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 722 views
  • 0 likes
  • 2 in conversation