SAS-Variable has been defined as both character and numeric

[Neal0801]

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.

%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;
  ;
  format  price volume bid_imp__vol Ask_Imp__Vol 8.;
  run;
%mend;


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

[Astounding]

Assigning a format to a variable controls how it will print.  It doesn't control whether it is character or numeric.

You're facing a different problem.  You have to change an existing variable.  You might have, for example, a data set where ZIPCODE is numeric, and a second data set where ZIPCODE is character.  You can't put them together without changing one of the data sets.  ZIPCODE must either be numeric in every data set or character in every data set.  Assigning a format has no impact.

[Neal0801]

So, I should use put() or input() function to change an existing variable, right?

[Astounding · Accepted solution]

Yes, but you can't change a variable's type.  You have to take a more roundabout approach.  For example, here are some DATA step statements that would change ZIPCODE from being numeric to being character:

zipchar = put(zipcode, z5.);

drop zipcode;

rename zipchar=zipcode;

That combination results in a new ZIPCODE variable that is character.  This would not be effective (it would leave ZIPCODE as numeric):

zipcode = put(zipcode, z5.);

[Neal0801]

%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.);

Hi, this is my new code that use input function. However, it still not working and reported same errors. Do you have any idea?

[Astounding]

Does the log indicate which variable(s) the error refers to?

[Neal0801]

As shown in the pic, the errors are still from the variables that I attempted to change. 

[Astounding]

Your code is trying to fix all of the data sets in one step.  You will have to switch gears, and fix each data set individually before trying to combine them.  For example:

%do i=1 %to &obs.;

data &source.&&memname&i;

set &source.&&memname&i;

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;

%end;

There will be more issues.  The above step should only take place when your incoming variables are character.  It should be skipped when they are already numeric.

After that ...

When you combine the data sets, you'll need to think about what data set name you want to use to hold the combination of all the data sets.  Right you, your code would store the combined data sets multiple times (look at the number of data set names in the DATA statement).

One step at a time, but remember that macro language is only trying to generate some SAS language code.  You have to verify that the generated SAS language code correctly carries out your intentions.

[Neal0801]

REALLY THANK YOU!! Thanks your advice. One step at a time!

[ballardw]

You may want to try combining a few of the data sets after you have "fixed" 3 or 4 to see if other issues arise. Then you could incorporate those fixes in each data set at the same time you a addressing the variable types.

[ballardw]

If your data sets are coming from Proc Import that is part of the cause: Import has to guess at the contents based on what appears in the data.

Since you are combining data sets that implies that they should have been similar or the same in content. So your step of reading the data into SAS data sets should have controlled this.

I suspect that after you get the ERROR fixed that you will get WARNING messages of "lengths of variables have been defined differently" and may result in truncation.

Might as well head that off now...