topic CAST like functionality in PROC SQL for UUID datatype in SAS Programming

CAST like functionality in PROC SQL for UUID datatype

Aexor — Thu, 23 Feb 2023 13:49:43 GMT

Hi All,

I want to know if we can use CAST like functionality in PROC SQL.

I have query where the internal query returns value which is in varchar format and outer query is having UUID datatype . So whenever I am trying to compare values it is throwing errors .

Thanks!

Re: CAST like functionality in PROC SQL for UUID datatype

sbxkoenk — Thu, 23 Feb 2023 14:00:00 GMT

Yes, you can !

There is :

CAST Function
CAST= Data Set Option
CAST= LIBNAME Statement Option

Just try it and let us know if you encounter any obstacles (which are only hurdles we can overcome).

Thanks,

Koen

Re: CAST like functionality in PROC SQL for UUID datatype

SASKiwi — Thu, 23 Feb 2023 21:02:59 GMT

Please post the SAS log of your PROC SQL step. I suspect you might be using SQL passthrough. If so use the CAST function of the external database.

Re: CAST like functionality in PROC SQL for UUID datatype

Aexor — Tue, 28 Feb 2023 10:30:43 GMT

No, I am not using sql passthrough .

Re: CAST like functionality in PROC SQL for UUID datatype

Aexor — Tue, 28 Feb 2023 10:32:42 GMT

proc sql noprint;
81 create table work.ids as
82 select distinct trim(lowcase(CAST (plan_id as uuid)))
--
22
202
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND,
BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||,
~, ~=.
ERROR 202-322: The option or parameter is not recognized and will be ignored.

Re: CAST like functionality in PROC SQL for UUID datatype

PaigeMiller — Tue, 28 Feb 2023 11:23:35 GMT

See the answer in this thread https://communities.sas.com/t5/SAS-Programming/Need-help-with-Proc-SQL-Cast/m-p/861242#M340203

Re: CAST like functionality in PROC SQL for UUID datatype

SASKiwi — Fri, 03 Mar 2023 22:02:17 GMT

In that case the SAS PUT function provides similar functionality.

Re: CAST like functionality in PROC SQL for UUID datatype

Patrick — Sat, 04 Mar 2023 00:13:40 GMT

SAS 9.4 only got two data types: Numeric and Character.

Varchar and UUID are database specific data types that don't exist in SAS. Which database are you interfacing with?

The discussion here shows you how to cast from uuid to varchar if using explicit pass-through SQL interfacing with Postgres.

If you transfer the data first to the SAS side then both UUID and VARCHAR get mapped to a SAS CHAR columns.

SAS/Access database specific docu like the one here for Postgres shows you what gets mapped how.

Please share your SQL and the related SAS log so we can understand what you're really trying to do and where things fail.

Re: CAST like functionality in PROC SQL for UUID datatype

Sajid01 — Sat, 04 Mar 2023 00:50:49 GMT

UUID or GUID is a 36 character /128 bit value. It is incomprehensible how one can cast a varchar value with a UUID value.
It will be wonderful if somebody enlightens on that possibility,.

Re: CAST like functionality in PROC SQL for UUID datatype

Patrick — Sat, 04 Mar 2023 05:41:57 GMT

@Sajid01 wrote:

UUID or GUID is a 36 character /128 bit value. It is incomprehensible how one can cast a varchar value with a UUID value.
It will be wonderful if somebody enlightens on that possibility,.

Not sure what point you're making. Why couldn't one store 36 characters in a Varchar? Have a look at below discussion.

https://communities.sas.com/t5/SAS-Programming/Error-in-PROC-SQL-query/m-p/859565#M339591

Re: CAST like functionality in PROC SQL for UUID datatype

Sajid01 — Sun, 05 Mar 2023 13:44:31 GMT

Thanks @Patrick .
What i was wondering how one can convert any a string value to UUID. Looks something improbable but wondering if there is a way.

Re: CAST like functionality in PROC SQL for UUID datatype

Tom — Sun, 05 Mar 2023 14:48:37 GMT

@Sajid01 wrote:

Thanks @Patrick .
What i was wondering how one can convert any a string value to UUID. Looks something improbable but wondering if there is a way.

If you want to generate a UUID value use the UUIDGEN() function. But the number generated has nothing to do with any input string.

If you want to associate the generated UUID with a specific character string then you will need to store that association in a separate dataset.

Re: CAST like functionality in PROC SQL for UUID datatype

Sajid01 — Sun, 05 Mar 2023 15:31:56 GMT

Thanks