SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How can I solve this error?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 88
Accepted Solution

How can I solve this error?

I am trying to merge a bunch of files, uisng proc sql, here is my code:

 

proc sql;
create table foot_all as
select*
from foot_paper_merged
outer union corr
select*
from foot_dados_paper_merge
outer union corr
select*
from foot_dados_ca_dc; 
quit;

 

but when I run this code I'm getting a number of errors of this nature:

 

ERROR: Numeric expression requires a numeric format.
ERROR: Column 83 from the first contributor of OUTER UNION is not the same type as its
counterpart from the second.

 

How can I solve this? There is a lot of them!!!


Accepted Solutions
Solution
‎06-27-2016 02:35 PM
SAS Super FREQ
Posts: 683

Re: How can I solve this error?

Yes you have to do the type conversion for all the columns. Maybe there is a way to change the program which creates the data before you combine it.

 

There is no automatic way to do the type conversion. The dictioinary table DICTIONARY.COLUMNS together with the SAS macro language can help you to achieve what you need.

 

Have a look here http://www.lexjansen.com/nesug/nesug10/ff/ff01.pdf or here http://www.sascommunity.org/wiki/Changing_Variable_Type for examples on how to do this.

 

Bruno

 

 

 

 

View solution in original post


All Replies
Super Contributor
Posts: 305

Re: How can I solve this error?

Hello,

 

As the error says you have column with the same name but of different type within the databases.

 

SAS Super FREQ
Posts: 683

Re: How can I solve this error?

[ Edited ]

Hi as the errorsays, you have different types for columns with the same name.

 

See the code below that illustrates this, it also shows you how you can list type and length for a column. you need to do a type conversion.

 

data have1;
  set sashelp.class;
run;

data have2;
  set sashelp.class(rename=(sex=sex_c age=age_n));
  sex = ifn(sex_c = "M", 1, 2);
  age = put(age_n, 8. -L);
  drop sex_c age_n;
run;

options nolabel;
proc sql;
  select
    libname
    , memname
    , lowcase(name) as name
    , type
    , length
    , varnum
  from
    dictionary.columns
  where
    libname = "WORK"
    and memname like "HAVE%"
  order by
    name
    , memname
  ;
quit;
options label;

proc sql;
  create table want as
  select
    *
  from
    have1
  outer union corr
  select
    *
  from
    have2
  ;
quit;

data want2;
  set have1 have2;
run;

 

Frequent Contributor
Posts: 88

Re: How can I solve this error?

Sorry I'm very new to sas so this is all over my head a bit...

 

There are a lot of columns that are giving me this error! Does that mean I have to do a type conversion for each one of these columns????

 

Initially I was usin the below code to convert some of my variables:

 

data foot_paper_dc_only;
set foot_paper_dc_only(rename=(DISCHARGE_RTW_STATUS=DISCHARGERTWSTATUSNUM));
DISCHARGE_RTW_STATUS = put(DISCHARGERTWSTATUSNUM, 3.);
drop DISCHARGERTWSTATUSNUM;
run;

 

Does that mean I have to do this for ALL the columns??? 

 

is there a way to just change everything? Is there an easier solution?

Solution
‎06-27-2016 02:35 PM
SAS Super FREQ
Posts: 683

Re: How can I solve this error?

Yes you have to do the type conversion for all the columns. Maybe there is a way to change the program which creates the data before you combine it.

 

There is no automatic way to do the type conversion. The dictioinary table DICTIONARY.COLUMNS together with the SAS macro language can help you to achieve what you need.

 

Have a look here http://www.lexjansen.com/nesug/nesug10/ff/ff01.pdf or here http://www.sascommunity.org/wiki/Changing_Variable_Type for examples on how to do this.

 

Bruno

 

 

 

 

Frequent Contributor
Posts: 88

Re: How can I solve this error?

Hi bruno, sorry to be a pain but I can't figure out how to deermine what column corresponds to what varibale name.

 

I've done a proc contens, but when I do this it spits out the variables in alphabetical order instead of column order #.

 

How can I spit out the variables sorted by column number so I can figure out which variables are giving me the error messagE?

 

thanks so much for your help really appreciate it!

Frequent Contributor
Posts: 88

Re: How can I solve this error?

nevermind I got it!

 

proc contents data=foot_paper_merged varnum;
run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 1325 views
  • 3 likes
  • 3 in conversation