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;
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.