trying to merge 4 datasets, errors with variable types etc

[missdoobi]

Hi there,

i'm trying to merge 4 datasets: baseline, month, year and endpoint by ID. i've sorted each dataset by ID but when i try to merge i get the error message:

variable has been defined as both character and numeric.

i don't really understand that error. i think it would be best to change it to a numeric type if it's asking me to choose between the two. but i'm not too sure how to do that either.

I then tried the RENAME function and tested that out by merging just baseline and year:
data baselineyear;
merge baseline
year (rename=(SBPC=YRSBPC DBPC=YDBPC HRC=YRHRC SBPA=YRSBPA DBPA=YRDBPA HRA=YRHRA));
by id;
run;

when i then used proc print to see if it worked, none of the numeric values showed, just the missing values which were '.' any numeric values in my excel sheet turned up as a blank space ' ' in proc print.

i'm really stumped and can't proceed forward until i've successfully merged all 4 datasets. Can anyone help me and tell me what statements/commands i need to use?

I've got a link below to the excel file,

http://www.box.net/shared/2cn7f11wko

if by chance it doesn't work, my email is missdoobiwhatsit@yahoo.com

 

[kkitt]

YOu will need to make sure that the 'ID' field is defined in the same format in all 4 dataset.

I did not get that message if they were all the same, as soon as I changed one from numeric to char then I get the convert error message.

 

[ChrisW75]

missdoobi, further to what kkitt says, the problem is that you have one or more fields with the same name, which appears on more than one dataset with different data types (1 character and 1 numeric).
First step, run proc contents on all 4 datasets and check to make sure that the ID field is the same data type on each dataset, then check to make sure you don't have any other fields with the same names on your datasets to be merged. If you do, you need to rename these. Merging datasets which have fields in common on them (other than the by variables) can yield some unexpected results.
To convert a numeric field to a character (my preferred method when using the field to join) use this :-

  length charfield $6;
  charfield = put(numericField,6.);

where 6 is the length of the longest number. In your case, if it's the ID field, set the length of the charfield to the same length as the other IDs. Change charfield to ID, and rename ID on the set statement to something else.
ie

data dset1b;
  set dset1(rename=(ID=ID_num));

  length ID $6;
  ID = put(ID_num,6.);
run;

To convert a character field to a numeric:-

  length numfield 6;
  numfield = input(charfield,8.;

Remember, the defined length of a numeric field is not the length of the number, but the number of bytes allocated to storing it.
I hope that this helps.


Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.