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.

 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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