topic Re: data merge question in SAS Programming

data merge question

stellapersis7 — Sat, 15 Oct 2022 04:04:54 GMT

Hi,

i have this 2 data sets to merge one full and only 'common' records from another data set.

i have used :

data merge1;

merge FY1.H181 (in=x) MCF.H180 (in=y);

by duid;

if (x=1);

run;

I did this after sorting using proc sort.

it gives me an error

"Variable DUID has been defined as both character and numeric"

but that variable has only numeric in the dataset.

can you please help me resolve thsi.

thanks

Re: data merge question

Kurt_Bremser — Sat, 15 Oct 2022 04:53:15 GMT

What the log says: duid is character in one dataset, and numeric in the other.

Run PROC CONTENTS for each dataset and look at the output.

Re: data merge question

stellapersis7 — Sat, 15 Oct 2022 16:08:50 GMT

HI thank you,

I did contents and yes, it has both charater and numeric like "prstx15".

now, how do i merge both datasets when it gives this error?

thanks in advance

Re: data merge question

stellapersis7 — Sat, 15 Oct 2022 16:09:59 GMT

HI thank you,
I did contents and yes, it has both charater and numeric
now, how do i merge both datasets when it gives this error?
thanks in advance

Re: data merge question

tarheel13 — Sat, 15 Oct 2022 16:41:27 GMT

You need to make duid the same type in both datasets. Either make them both numeric or both character.

Re: data merge question

Kurt_Bremser — Sat, 15 Oct 2022 17:29:33 GMT

In the SQL procedure, you can use functions in the ON clause of a JOIN, so you can do the conversion there "on the fly".

For a data step MERGE, you need to first run a conversion step before you sort and merge.

How you do that depends on how the data is stored as character. I recommend to convert the numbers to character, as key values are always better stored as character.