SAS error thinking a variable is defined as both character and numeric

Question

This is occurring in SAS Studio. I'll keep this as simple as possible. This problem has been driving me crazy for hours now. I've searched for similar issues and found none involving datasets as simple as the ones I'm working with.

I have an existing dataset from earlier in the program. I have another dataset that I imported from an xls file. I'm trying to merge these two together based off the variable that SAS is telling me is defined as both character and numeric.

The character variable is a string of length 10 that is entirely numbers. It's a character variable because I need to retain leading zeros.

The following code is what I've done to absolutely ensure that each variable I want is a character. It still doesn't work:

DATA TEST1;
SET earlier_data;
TESTING=input(want,$10.);
RUN;

DATA TEST2;
SET xls_import;
TESTING=input(want,$10.);
RUN;

PROC SORT DATA=TEST1;
BY TESTING;
RUN;

PROC SORT DATA=TEST1;
BY TESTING;
RUN;

DATA TEST3;
MERGE TEST1 (in=a) TEST2 (in=b);
BY TESTING;
RUN;

Despite running a PROC CONTENTS on both data sets and confirming that TESTING is a character variable of length 10. I still get the error:

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

Any help would be greatly appreciated. Thank you.

It does really make much sense to use INPUT() with a character informat like you have. Why not just use the SUBSTRN() function instead? That would do the same thing in a much more obvious way. Or were you trying to convert the strings in WANT into a number? In that case use a numeric informat instead of a character informat. Or if you are trying to convert the number in WANT into a string then use the PUT() function with a numeric format, such as Z10. that would include leading zeros. — Tom
– Tom, Commented Aug 4, 2022 at 18:00

Tom · Accepted Answer · 2022-08-04 18:06:47Z

1

Do not use the name of an existing variable as the name you specify on the IN= dataset option.

Use a name that does not already exist in the data step.

DATA TEST3;
  MERGE TEST1 (in=in_test1) TEST2 (in=in_test2);
  BY TESTING;
RUN;

answered Aug 4, 2022 at 18:06

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Peetrius Over a year ago

Holy moly you fixed it. Thank you so much. The excel sheet was bringing in extra columns with the variables named after the letters. I cannot thank you enough.

Reeza Over a year ago

@Peetrius given that you didn't post actual code, it's pretty close to magic for sure :)

Peetrius Over a year ago

Yeah I felt bad I didn't have the code available. This is my personal account troubleshooting code I'm using for work, so it it would have taken a long time to sanitize everything to post a log. I'm glad it all worked out in the end.

Collectives™ on Stack Overflow

SAS error thinking a variable is defined as both character and numeric

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related