char varchar conflict when doing data stet set operation in CAS

acordes — Thu, 27 Apr 2023 08:37:31 GMT

I often run into the following problem.

Depending on how I had loaded the table to a caslib, some variables get varchar and others char.

My only trick to overcome this is to make a copy of both tables in the work library and afterwords load them from there to the caslib again.

But this is not very smart.

I don't know how to assign char to varchar or the other way round.

82   data RISKNOBA.EUROTAX_FC_EXT_APR23;
83   set RISKNOBA.EUROTAX_FC_EXT_APR23(where=(seg ne '')) RISKNOBA.TEMPUS;
ERROR: Variable seg has been defined as both character and varchar.
ERROR: Variable subseg has been defined as both character and varchar.
84   run;

So my workaround is too complicated, it works but I'm not proud of this piece of code.

The context is that I have a table that needs to be updated because I have a newer version of the format table where the variables seg and subseg come from. I tried to use fedsql with the UPDATE or union syntax but I didn't succeed. So I go the long way.

But what bothers me most is that I get this varchar character conflict when I do a data step set operation in CAS.

One idea would be to assign a format with the first fedsql code, but I don't know how.

data RISKNOBA.tempus;
set RISKNOBA.EUROTAX_FLEET_EXT_APR23(where= (seg=''));
drop seg subseg found_msi;
run;



proc cas;
source MPG_toyota;
    create table RISKNOBA.TEMPUS{options replace=true} as 
      select a.*, b.seg, b.subseg, case when seg='' then 0 else 1 end as found_msi 
               from RISKNOBA.TEMPUS a left join RISKNOBA.MSI_ALL b 
      on a.bastidor=b.bastidor  ;
endsource;
fedSQL.execDirect / query=MPG_toyota;
quit;

options casdatalimit=all;
data tempus;
set RISKNOBA.TEMPUS;
run;

data RISKNOBA.TEMPUS;
set tempus;
run;

data tempus;
set RISKNOBA.EUROTAX_FLEET_EXT_APR23(where=(seg ne ''));
run;

data RISKNOBA.EUROTAX_FLEET_ALL_EXT_APR23;
set tempus;
run;


data RISKNOBA.EUROTAX_FLEET_ALL_EXT_APR23(promote=yes);
set RISKNOBA.EUROTAX_FLEET_ALL_EXT_APR23(where=(seg ne '')) RISKNOBA.TEMPUS;
run;

Re: char varchar conflict when doing data stet set operation in CAS

Patrick — Thu, 27 Apr 2023 12:35:08 GMT

Varchar and Char are different data types so you need to cast one to the other. With SQL syntax that's an ALTER TABLE and not just a format.

Of course if you pull the data from CAS to SPRE then the v9 engine doesn't "know" Varchar and converts it automatically to CHAR. Same story as when interfacing with a database.

CAS with its additional data types brings us a bit closer to how one needs to work within any data base.

topic Re: char varchar conflict when doing data stet set operation in CAS in SAS Programming

char varchar conflict when doing data stet set operation in CAS

Re: char varchar conflict when doing data stet set operation in CAS