topic Re: How can I solve this error? in SAS Data Management

How can I solve this error?

christinagting0 — Fri, 24 Jun 2016 13:33:36 GMT

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!!!

Re: How can I solve this error?

Loko — Fri, 24 Jun 2016 13:49:25 GMT

Hello,

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

Re: How can I solve this error?

BrunoMueller — Fri, 24 Jun 2016 13:53:51 GMT

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;

Re: How can I solve this error?

christinagting0 — Fri, 24 Jun 2016 13:58:56 GMT

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?

Re: How can I solve this error?

BrunoMueller — Fri, 24 Jun 2016 14:18:07 GMT

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

Re: How can I solve this error?

christinagting0 — Fri, 24 Jun 2016 15:00:37 GMT

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!

Re: How can I solve this error?

christinagting0 — Fri, 24 Jun 2016 15:03:18 GMT

nevermind I got it!

proc contents data=foot_paper_merged varnum;
run;