SAS-Variable has been defined as both character and numeric

Question

I have 100 datasets in a library (called DATA). When I want to merger them into one dataset, SAS said some variables defined as both character and numeric. So I used following codes to modify the problem through change variables' format. However, SAS still reported the error: Variable has been defined as both character and numeric when I attempting to change their formats.

edit: I changed my code and use input function to solve this problem. However, it reported same errors as shown in the pic:

%macro step1(sourcelib=,source=); 
proc sql noprint;  /*read datasets in a library*/
  create table mytables as
  select *
  from dictionary.tables
  where libname = &sourcelib
  order by memname ;

  select count(memname) 
  into:obs 
  from mytables;

  %let obs=&obs.;

  select memname
  into : memname1-:memname&obs.
  from mytables;
quit;

  data 
  %do i=1 %to &obs.;
  &source.&&memname&i
  %end;
  ;
  set
  %do i=1 %to &obs.;
  &source.&&memname&i
  %end;
  ;
 price1=input(price,best12.);
 volume1=input(volume,best12.);
 bid_imp__vol1=input(bid_imp__vol,best12.);
 Ask_Imp__Vol1=input(Ask_Imp__Vol,best12.);
 drop price volume bid_imp__vol ask_Imp__Vol;
  run;

data 
  %do i=1 %to &obs.;
  &source.&&memname&i
  %end;
  ;
  set
  %do i=1 %to &obs.;
  &source.&&memname&i
  %end; 
  (rename=(price1=price volume1=volume bid_imp__vol1=bid_imp__vol Ask_Imp__Vol1=Ask_Imp__Vol));
  run;
%mend;


 %step1(sourcelib='DATA',source=DATA.);

Joe Joe · Accepted Answer · 2016-10-26T15:22:34

FORMAT has nothing whatsoever to do with the issue. Type is the issue. Format is "how do you want me to print this out in human readable format", but Type is "what is fundamentally in this block of memory".

You'll need to do one of three things here.

Drop the conflicting variables.
Rename the conflicting variables, either in a previous step or in the SET dataset options.
Change the type of the conflicting variables, either in a previous step or through renaming them in the SET dataset options and then converting to the proper type.

In a macro like the above, one thing you can do is to use dictionary.columns to determine what variables are the wrong type. Since you have a limited set of variables it looks like (i.e., this doesn't need to be totally generic), you can just pick one type (sounds like numeric) and query dictionary.columns for any variables that are not of that type. Then apply the conversion for those that meet the criteria (of being character).

The way you constructed your macro is going to make this a bit more complex though; I think you may want to have a separate macro that goes through each dataset and converts its type to the consistent type, one at a time, before you run this macro at all. Otherwise it's going to be a headache to manage the macro variable lists.

SAS-Variable has been defined as both character and numeric

1 Answers