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.
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.
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.
Thank you so much, It worked for me.
And now I am on the second challenge.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.