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

[kmardinian]

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=ID;). 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!

[Reeza]

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

[kmardinian]

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

[Reeza]

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;

[kmardinian]

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!

[Astounding]

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.

[kmardinian]

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

[Reeza]

---

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

[kmardinian]

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

[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?

[kmardinian]

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

[Reeza · Accepted solution]

---

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

[kmardinian]

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