not able to resolve conversion errors

nikhilbajaj — Fri, 08 Aug 2014 17:23:42 GMT

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.

Re: not able to resolve conversion errors

DBailey — Fri, 08 Aug 2014 18:02:05 GMT

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.

Re: not able to resolve conversion errors

nikhilbajaj — Fri, 08 Aug 2014 18:29:01 GMT

Thank you so much, It worked for me.

And now I am on the second challenge.

topic Re: not able to resolve conversion errors in SAS Procedures

not able to resolve conversion errors

Re: not able to resolve conversion errors

Re: not able to resolve conversion errors