CAST like functionality in PROC SQL for UUID datatype

[Aexor]

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!

[sbxkoenk]

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

[Aexor]

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.

[PaigeMiller]

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

[SASKiwi]

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.

[Aexor]

No, I am not using sql passthrough .

[SASKiwi]

In that case the SAS PUT function provides similar functionality.

[Patrick]

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.

[Sajid01]

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,.

[Patrick]

---

@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 

[Sajid01]

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.

[Tom]

---

@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.

[Sajid01]

Thanks