DATA Step, Macro, Functions and more

ERROR: ID is has been defined as both character and numeric

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

ERROR: ID is has been defined as both character and numeric

 

proc sort data= G out=G1;
by ID;
run;
proc sort data= P out=P1;
by ID;
run;
Proc sort data=T out=T1;
by ID;
Run;

Data CombinedData;
Merge G1 P1;
By ID;
Run;

Data MCC1;
Set CombinedData (keep= Name ID protocol);
If missing(protocol) then delete;
run;

Data T1;

IDchar = put(ID, $8.);
drop ID;
rename IDchar=ID;
Run;

Data BT;
Merge T1 MCC1;
By ID Name;
If inT1 and inMCC1;
Run;

 

Here is my code above, I've been getting this error for the ID variable where SAS states that it is not defined as character or numeric, so I added in a part of the code above to try to transform the ID variable (IDchar = put(ID, $8.);
drop ID; rename IDchar=IDSmiley Wink. But now my Data BT just shows up blank and no observations are printed or read through SAS. Any help is much appreciated, I am still pretty new to SAS. Thank you!


Accepted Solutions
Solution
‎10-10-2017 03:31 PM
Super User
Posts: 24,027

Re: ERROR: ID is has been defined as both character and numeric

Posted in reply to kmardinian

kmardinian wrote:

What's strange is the ID in both excel sheets are in the exact same format. The cells are formatted as "General" and the ID numbers are all 8 numbers long. So I'm not sure why SAS is reading them as different?


PROC IMPORT doesn't do a great job at reading data from Excel. If you can, consider using a text file, csv, instead. Then you can specify the types. Excel doesn't enforce types so it's not a good medium for storing and transferring data. 

View solution in original post


All Replies
Super User
Posts: 24,027

Re: ERROR: ID is has been defined as both character and numeric

Posted in reply to kmardinian

Verify that your INPUT() conversion worked correctly. I suspect it didn't.

Frequent Contributor
Posts: 82

Re: ERROR: ID is has been defined as both character and numeric

[ Edited ]

I used proc import to import the excel datasets and they seem to have imported correctly.

Super User
Posts: 24,027

Re: ERROR: ID is has been defined as both character and numeric

Posted in reply to kmardinian

Data BT;
Merge T1 MCC1;
By ID Name;
If inT1 and inMCC1; *<- this line is incorrect, you have no variables created with these names;
Run;

Frequent Contributor
Posts: 82

Re: ERROR: ID is has been defined as both character and numeric

I see, I assumed sas would pull the variables from the excel spreadsheet. What would be the easiest way to do that? Should I use a Keep statement when merging the two datasets?

Thank you!

Super User
Posts: 6,939

Re: ERROR: ID is has been defined as both character and numeric

Posted in reply to kmardinian

Let's narrow down the problem a little.  Are these the right issues?

 

  • You have an existing data set named T1, that contains a numeric variable named ID.
  • To be able to merge with other data sets, you need to change ID to character (within the T1 data set).

Does that sound about right?  If so ...

 

First, note that your existing data step that re-generates T1 does not contain a SET statement.  Where is the data supposed to come from?  You might need:

 

data T1;

set T1

IDchar = .......

 

Next, one of these formulas would be better:

 

IDchar = put(ID, z8.);

 

or possibly:

 

IDchar = put(ID, 8.);

IDchar = left(IDchar);

 

You might have to see what ID looks like within MCC1 to determine which of these makes sense.  Whichever one looks right, you still need your DROP and RENAME statements.

Frequent Contributor
Posts: 82

Re: ERROR: ID is has been defined as both character and numeric

Posted in reply to Astounding

Data T2;
Set T1 (keep= ID Name);
IDchar = put(ID, z8.);
drop ID;
rename IDchar=ID;
Run;

Data BT (keep= ID Name);
Merge T2 MCC1;
By ID;
*If inT2 and inMCC1;
Run;

 

So adding in the set statement I think worked! But it did give me a warning saying "WARNING: Variable MRN has already been defined as numeric." Does that still mean it's ok?

 

But now I have also another issue, when I merge T2 and MCC1 to create the dataset BT, I lose the ID the Names from MCC1...I took out my "if inT2 and inMCC1" statement for now because it is still giving me errors

Super User
Posts: 24,027

Re: ERROR: ID is has been defined as both character and numeric

Posted in reply to kmardinian

kmardinian wrote:

 

So adding in the set statement I think worked! But it did give me a warning saying "WARNING: Variable MRN has already been defined as numeric." Does that still mean it's ok?

 

But now I have also another issue, when I merge T2 and MCC1 to create the dataset BT, I lose the ID the Names from MCC1...I took out my "if inT2 and inMCC1" statement for now because it is still giving me errors


No, it's not ok. You can use the log to find syntax errors, but you need to confirm your data meets what you expect by examining the data. We can't really tell you what's correct or not logically.

 

Frequent Contributor
Posts: 82

Re: ERROR: ID is has been defined as both character and numeric

LOG:

 

 
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
61
62
63 Data T2;
64 Set T1 (keep= ID Name);
65 IDchar = put(ID, z8.);
WARNING: Variable ID has already been defined as numeric.
66 drop ID;
67 rename IDchar=ID;
68 Run;
 
NOTE: There were 26932 observations read from the data set WORK.T1.
NOTE: The data set WORK.T2 has 26932 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
 
 
69
70
71 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
84
 
The Log isn't giving me too much information, so it probably didn't work in fixing this issue. I'm not really sure what next steps to take
Frequent Contributor
Posts: 82

Re: ERROR: ID is has been defined as both character and numeric

Posted in reply to kmardinian

What's strange is the ID in both excel sheets are in the exact same format. The cells are formatted as "General" and the ID numbers are all 8 numbers long. So I'm not sure why SAS is reading them as different?

Frequent Contributor
Posts: 82

Re: ERROR: ID is has been defined as both character and numeric

Posted in reply to kmardinian

If I were to change ID to just be read as character, where would it best to write that code?

Solution
‎10-10-2017 03:31 PM
Super User
Posts: 24,027

Re: ERROR: ID is has been defined as both character and numeric

Posted in reply to kmardinian

kmardinian wrote:

What's strange is the ID in both excel sheets are in the exact same format. The cells are formatted as "General" and the ID numbers are all 8 numbers long. So I'm not sure why SAS is reading them as different?


PROC IMPORT doesn't do a great job at reading data from Excel. If you can, consider using a text file, csv, instead. Then you can specify the types. Excel doesn't enforce types so it's not a good medium for storing and transferring data. 

Frequent Contributor
Posts: 82

Re: ERROR: ID is has been defined as both character and numeric

Ok, thank you! I was able to get it to work.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 1150 views
  • 0 likes
  • 3 in conversation