BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

6 REPLIES 6
Loko
Barite | Level 11

Hello,

 

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

 

BrunoMueller
SAS Super FREQ

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;

 

christinagting0
Quartz | Level 8

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?

BrunoMueller
SAS Super FREQ

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

 

 

 

 

christinagting0
Quartz | Level 8

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!

christinagting0
Quartz | Level 8

nevermind I got it!

 

proc contents data=foot_paper_merged varnum;
run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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