BookmarkSubscribeRSS Feed
acordes
Rhodochrosite | Level 12

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;

 

 

 

 

 

1 REPLY 1
Patrick
Opal | Level 21

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.

 

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 837 views
  • 1 like
  • 2 in conversation