SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

"ERROR: Variable has been defined as both character and numeric" with multiple datasets using Macro

Reply
Occasional Contributor
Posts: 10

"ERROR: Variable has been defined as both character and numeric" with multiple datasets using Macro

Hello!

Basically I need to import multiple excel files and simply stack them. I wrote a Macro to do that, but at the end of the code where it comes to the stack part, multiple error messages appear saying:

ERROR: Variable XX has been defined as both character and numeric.

I tried to modify format in the Macro, but it doesn't work.

Will you please take a look at my code and let me know how I should modify it?

Here's the code:

 

%LET TOTAL=4;

%LET PATH=H:\test\;

%LET INFILE1=a (1).xlsx;
%LET INFILE2=a (2).xlsx;
%LET INFILE3=a (3).xlsx;
%LET INFILE4=a (4).xlsx;

    %MACRO EXCELREAD(I,INFILE);
    PROC IMPORT OUT=TEST_&i
                DATAFILE="&PATH.&INFILE"
                DBMS=EXCEL REPLACE; 
         GETNAMES=NO;
         MIXED=YES;
         SCANTEXT=YES;
         USEDATE=YES;
         SCANTIME=YES;
    RUN;
    %MEND;
    %EXCELREAD(1,&INFILE1);
    %EXCELREAD(2,&INFILE2);
    %EXCELREAD(3,&INFILE3);
    %EXCELREAD(4,&INFILE4);


data pilot_bond;
	set test_1		
	    test_2(firstobs=2)
	    test_3(firstobs=2)
	    test_4(firstobs=2)
run;

 

Here's the error message:

 

3712  data pilot_bond;
3713      set test_1
3714          test_2(firstobs=2)
3715          test_3(firstobs=2)
3716          test_4(firstobs=2)

ERROR: Variable F1 has been defined as both character and numeric.
ERROR: Variable F2 has been defined as both character and numeric.
ERROR: Variable F6 has been defined as both character and numeric.
ERROR: Variable F7 has been defined as both character and numeric.
ERROR: Variable F6 has been defined as both character and numeric.
ERROR: Variable F6 has been defined as both character and numeric.
3730  run;

 

 

Any help will be greatly appreciated!

Sugguestions to further simplifying the code is also very welcome.

Regular Contributor
Posts: 161

Re: "ERROR: Variable has been defined as both character and numeric" with multiple dataset

[ Edited ]
Posted in reply to BellaLuna

Have you tried a PROC SQL SELECT with a UNION?

Kannan Deivasigamani
Occasional Contributor
Posts: 10

Re: "ERROR: Variable has been defined as both character and numeric" with multiple dataset

Hi, I'm not familiar with sql language, would you mind iterate? Thank you!
Super User
Super User
Posts: 7,942

Re: "ERROR: Variable has been defined as both character and numeric" with multiple dataset

Posted in reply to BellaLuna

Well, again we see this Excel issue.  This is covered in at least 3 posts in the last couple of days.  Excel is not a good data transfer format, it is unstructured.  Proc import is a guessing procedure.  What you are finding is there is either some element in the Excel document, say all you column data is numeric other than one row which has a character in it.  Between the dubious data medium, and proc import "guessing" what the result should be, the data is not as expected.  If you ope the properties of the test1-test4 dataset you will see what type each variable is associated with, either test or numeric.  You will find at least one column has different types across that data.  Now I am not going to go over the point again on this thread, please read: 

https://communities.sas.com/t5/General-SAS-Programming/PROC-IMPORT-from-Excel-how-to-ensure-consiste...

 

Which describes your problem.  There are other possiblities (such as data cleaning on you excel file, or converting data in your SAS program), but my suggestions are given in that post = save the data in a proper data transfer format, write a datastep import where you - knowing your data - specify what is to be read in and how.

Occasional Contributor
Posts: 10

Re: "ERROR: Variable has been defined as both character and numeric" with multiple dataset

Hello, 

Thanks for the reply. I understand the error msg and what caused it, but I need advice on how to solve it under my macro codes, instead of doing it one excel sheet by another, becuase I have hunderds of excel sheets. Any sugguestions on revising the code?

Super User
Posts: 11,343

Re: "ERROR: Variable has been defined as both character and numeric" with multiple dataset

Posted in reply to BellaLuna

To control how the data are read consisitently, especially if you have lots of files that are supposed to be in the same layout, then your best bet is to search this forum for one of the EXCEL macros to convert files to CSV and then write a program to read them.

 

Or have the supplier of the data provide CSV.

 

Note that if you have dates in the data you may have the extra joy of dealing with dates in different formats within a single column/variable.

The SAS informat ANYDTDTE may help in that case.

Super User
Super User
Posts: 7,942

Re: "ERROR: Variable has been defined as both character and numeric" with multiple dataset

[ Edited ]
Posted in reply to BellaLuna

Its not something you can change just like that.  You need to examine the import datasets as I said, find out which dataset has a different column type.  Then look at the data which is causing this, and either fix the data, do a proper import, or write additional code to process the differing column into the same column type.  So if you have:

Test1

VAR1   Num

VAR2   Text

 

Test2

VAR1   Text

VAR2   Text

 

Then you need to look at the data which is in VAR in the spreadsheet tab test2, does it contain non-numeric values, are the numbers stored as text.  Proc import has looked at that sheet and decided that VAR1 in that sheet has to be character for some reason.  This is one really good reason to use a proper data transfer format.

 

Oh, one other thing, the union tip given above works by taking the first select column's format, and then appending the next to it.  You could end up losing data if it doesn't convert.

Regular Contributor
Posts: 161

Re: "ERROR: Variable has been defined as both character and numeric" with multiple dataset

Posted in reply to BellaLuna

Hello,

 

I tried out this logic and seemed to work ..... may be a round about way but, I believe it might work for what you are trying to do...

 

I have created 2 dummy files within my datalines. One file reads the var as numeric (first file) and the second file reads the same variable as character so as to simulate what you have in your EXCEL import. So , now, we have 2 datasets having the same variable defined as char and numeric. What this code does it that irrespective of the type of data definition, it just writes into a text file and reads. In the next step reads the data as character so all data is converted to character. Once you have every file processed through the same cycle, you will have all files in character format and set to one dataset.   At the end, you can do one formatting of the variables as you need it as either numeric or char per your requirement in the output.  For efficiency and space concerns, the macro reuses the same work file and you may chose to use the same sas dataset name as your imported dataset name if you like to reuse the same name; in this example, I've isolated it separately as ds1, ds2 which you may reuse as in1, in2.

 

filename file1 '/folders/myfolders/testfile.txt';
data in1;input v1:1.;
datalines;
1
2
3
;
RUN;
data in2;input v1:$1.;
datalines;
1
4
5
6
;
RUN;
%macro read_as_char(indsn,workfilename,outdsname);
data _null_;
     file &workfilename;
     set &indsn;
     put v1;
run;
data &outdsname;
     infile &workfilename;
     input v1:$1.;
RUN;
%mend read_as_char;
%read_as_char(in1,file1,ds1);
%read_as_char(in2,file1,ds2);

data final;
     set ds1 
         ds2;
run;
proc print data=final;
proc contents data=final;
run;

Here are the results displayed for your reference:

 


Obs	v1
1	1
2	2
3	3
4	1
5	4
6	5
7	6
The CONTENTS Procedure
Data Set Name	WORK.FINAL	Observations	7
Member Type	DATA	Variables	1
Engine	V9	Indexes	0
Created	10/30/2015 10:52:28	Observation Length	1
Last Modified	10/30/2015 10:52:28	Deleted Observations	0
Protection	 	Compressed	NO
Data Set Type	 	Sorted	NO
Label	 	 	 
Data Representation	SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64	 	 
Encoding	utf-8 Unicode (UTF-8)	 	 
Engine/Host Dependent Information
Data Set Page Size	65536
Number of Data Set Pages	1
First Data Page	1
Max Obs per Page	58218
Obs in First Data Page	7
Number of Data Set Repairs	0
Filename	/tmp/SAS_work44E50000605B_localhost.localdomain/SAS_work15C70000605B_localhost.localdomain/final.sas7bdat
Release Created	9.0401M3
Host Created	Linux
Inode Number	275746
Access Permission	rw-rw-r--
Owner Name	sasdemo
File Size	128KB
File Size (bytes)	131072
Alphabetic List of Variables and Attributes
#	Variable	Type	Len
1	v1	Char	1

Hope this helps.... Good luck...!!!

Kannan Deivasigamani
Super User
Super User
Posts: 7,942

Re: "ERROR: Variable has been defined as both character and numeric" with multiple dataset

Which is fine, so long as the data the person has is actually convertable, I mean change this part of your program and you will see wanring about data not converting for instance.

data in2;input v1:$1.;
datalines;
1
4
A
6
;
RUN;

 

Not to mention all the other possible problems with Excel data.

Regular Contributor
Posts: 161

Re: "ERROR: Variable has been defined as both character and numeric" with multiple dataset

Agreed. It can get complicated when there are other types including PD, IB, etc. 

Kannan Deivasigamani
Ask a Question
Discussion stats
  • 9 replies
  • 854 views
  • 0 likes
  • 4 in conversation