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

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.);
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

 

 

View solution in original post

10 REPLIES 10
Astounding
PROC Star

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
Obsidian | Level 7
So, I should use put() or input() function to change an existing variable, right?
Astounding
PROC Star

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
Obsidian | Level 7
%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
PROC Star

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

Neal0801
Obsidian | Level 7

11111.png

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

Astounding
PROC Star

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
Obsidian | Level 7
REALLY THANK YOU!! Thanks your advice. One step at a time!
ballardw
Super User

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
Super User

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

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 19163 views
  • 3 likes
  • 3 in conversation