BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nikhilbajaj
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10

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

2 REPLIES 2
DBailey
Lapis Lazuli | Level 10

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.

nikhilbajaj
Calcite | Level 5

Thank you so much, It worked for me.

And now I am on the second challenge.

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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