0

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.

Peetrius
  • 203
  • 1
  • 9
  • Show the log from the code. – Reeza Aug 04 '22 at 17:32
  • 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 Aug 04 '22 at 18:00

1 Answers1

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;
Tom
  • 47,574
  • 2
  • 16
  • 29
  • 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. – Peetrius Aug 04 '22 at 18:10
  • 1
    @Peetrius given that you didn't post actual code, it's pretty close to magic for sure :) – Reeza Aug 04 '22 at 22:39
  • 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. – Peetrius Aug 09 '22 at 13:38