BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RShaw
Calcite | Level 5

Hello, 

 

So I am attempting to run code that merges two data set and sorts by the STUDYID variable. Unfortunately, the merge cannot run because the STUDYID variable has been identified as both character and numeric: 

 

Error Both Character and Numeric.png

 

I have attempted to create a new variable STUDYIDnumeric which converts the STUDYID variable to just numeric via the code 

STUDYIDnumeric = STUDYID*1.0. I have also tried using the code STUDYIDnumeric = INPUT(STUDYID*1.0)...

 

Anyway, here is the code I used. 

 

proc sort data = ITCH.CrossSection1; by STUDYID;
proc sort data = ITCH.CROSS_ICD9_Pre; by STUDYID;
proc sort data = ITCH.CROSS_ICD9_Post; by STUDYID;
data ITCH.CROSS_MERGED;
STUDYIDnumeric = STUDYID*1.0;
merge ITCH.CrossSection1(in=A) ITCH.CROSS_ICD9_Pre ITCH.CROSS_ICD9_Post;
by STUDYIDnumeric;
if A;

run;

 

However, upon running that code, I still keep getting an error code: 

Error Both Character and Numeric 2.PNG

 

 What should I do next? I've been using SAS for about 1 week.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

The first question you should really ask is how did you end up with STUDYID defined differently in the two datasets.  If you fix the problem at the source then you will not have these types of problems later on.  

 

If the values can be numbers then you will have less problems if you convert them all to numbers. The problem with id values that are converted to numbers is that leading zeros disappear.  For numbers leading zeros don't matter, 00123 is the same as 123. But for character strings '00123' does not equal '123'.

 

You will need to convert the variable before trying to use it in a merge.  Convert first and then sort because numbers and character strings will likely sort into different orders.

 

* STUDYID is character ;
data fix1 ;
   set ITCH.CrossSection1 ;
   studyid_num = input(studyid,32.);
   drop studyid ;
run;

* STUDYID is character ;
data fix2 ;
   set ITCH.CROSS_ICD9_Post;
   studyid_num = input(studyid,32.);
   drop studyid ;
run;

* STUDYID is already numeric ;
data fix3 ;
   set ITCH.CROSS_ICD9_Pre;
   studyid_num = studyid;
   drop studyid ;
run;


proc sort data=fix1 ; by studyid_num ; run;
proc sort data=fix2 ; by studyid_num ; run;
proc sort data=fix3 ; by studyid_num ; run;

data ITCH.CROSS_MERGED;
  merge fix1 (in=in1) fix2 fix3 ;
  by studyid_num ;
  if in1;
run;

 

View solution in original post

3 REPLIES 3
Reeza
Super User
StudyID_Num = input(studyID, best12.);

@RShaw wrote:

Hello, 

 

So I am attempting to run code that merges two data set and sorts by the STUDYID variable. Unfortunately, the merge cannot run because the STUDYID variable has been identified as both character and numeric: 

 

Error Both Character and Numeric.png

 

I have attempted to create a new variable STUDYIDnumeric which converts the STUDYID variable to just numeric via the code 

STUDYIDnumeric = STUDYID*1.0. I have also tried using the code STUDYIDnumeric = INPUT(STUDYID*1.0)...

 

Anyway, here is the code I used. 

 

proc sort data = ITCH.CrossSection1; by STUDYID;
proc sort data = ITCH.CROSS_ICD9_Pre; by STUDYID;
proc sort data = ITCH.CROSS_ICD9_Post; by STUDYID;
data ITCH.CROSS_MERGED;
STUDYIDnumeric = STUDYID*1.0;
merge ITCH.CrossSection1(in=A) ITCH.CROSS_ICD9_Pre ITCH.CROSS_ICD9_Post;
by STUDYIDnumeric;
if A;

run;

 

However, upon running that code, I still keep getting an error code: 

Error Both Character and Numeric 2.PNG

 

 What should I do next? I've been using SAS for about 1 week.




 

RShaw
Calcite | Level 5

Hi, so I tried making the simple code change you suggested; unfortunately it did not work: 

 

Changing Code.PNG

 

 

 

Tom
Super User Tom
Super User

The first question you should really ask is how did you end up with STUDYID defined differently in the two datasets.  If you fix the problem at the source then you will not have these types of problems later on.  

 

If the values can be numbers then you will have less problems if you convert them all to numbers. The problem with id values that are converted to numbers is that leading zeros disappear.  For numbers leading zeros don't matter, 00123 is the same as 123. But for character strings '00123' does not equal '123'.

 

You will need to convert the variable before trying to use it in a merge.  Convert first and then sort because numbers and character strings will likely sort into different orders.

 

* STUDYID is character ;
data fix1 ;
   set ITCH.CrossSection1 ;
   studyid_num = input(studyid,32.);
   drop studyid ;
run;

* STUDYID is character ;
data fix2 ;
   set ITCH.CROSS_ICD9_Post;
   studyid_num = input(studyid,32.);
   drop studyid ;
run;

* STUDYID is already numeric ;
data fix3 ;
   set ITCH.CROSS_ICD9_Pre;
   studyid_num = studyid;
   drop studyid ;
run;


proc sort data=fix1 ; by studyid_num ; run;
proc sort data=fix2 ; by studyid_num ; run;
proc sort data=fix3 ; by studyid_num ; run;

data ITCH.CROSS_MERGED;
  merge fix1 (in=in1) fix2 fix3 ;
  by studyid_num ;
  if in1;
run;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1002 views
  • 0 likes
  • 3 in conversation