topic Re: Merging two datasets in SAS Programming

Merging two datasets

aabbccwyt — Wed, 04 Dec 2019 04:14:36 GMT

Hi, I'm trying to merge two datasets by the same variable AcctNums:

data NewAcctNum;
        set NewAcctNum;
	merge Location_Data Purchases;
	by AcctNums;
run;

And it gives me "Variable AcctNum has been defined as both character and numeric.". I have already changed the variable AcctNum to a Numeric variable so I have no idea what I have done wrong. Can someone help please? Thank you. (It was a 1-1 merging.)

Re: Merging two datasets

Jagadishkatam — Wed, 04 Dec 2019 04:23:10 GMT

You cannot use the set statement in merge , try the below code

data NewAcctNum;
	merge Location_Data Purchases;
	by AcctNums;
run;

Re: Merging two datasets

aabbccwyt — Wed, 04 Dec 2019 04:31:53 GMT

Hi Jag. Thank you again. I tried it however it is giving me the same thing.

 112        /*Merge the two datasets*/
 113        data NewAcctNum;
 114        merge Location_Data Purchases;
 ERROR: Variable AcctNum has been defined as both character and numeric.
 115        by AcctNums;
 116        run;

I'm wondering if there is a way to specify whether it's character or numeric.

Re: Merging two datasets

Jagadishkatam — Wed, 04 Dec 2019 04:38:18 GMT

Unless i see that data i may not provide the solution,but you should try and check if the variable in location_data or purchases dataset has the acctnum as character or numeric , it should be same in both the datasets.

Re: Merging two datasets

unison — Wed, 04 Dec 2019 05:35:08 GMT

Does the error say “Acctnum...” or “Acctnums...”? Your merge step says Acctnums but the error you detail lists Acctnum.

be sure that you’re merging on the correct variable.

-unison

Re: Merging two datasets

andreas_lds — Wed, 04 Dec 2019 07:36:56 GMT

@aabbccwyt wrote:

Hi Jag. Thank you again. I tried it however it is giving me the same thing.
 112        /*Merge the two datasets*/
 113        data NewAcctNum;
 114        merge Location_Data Purchases;
 ERROR: Variable AcctNum has been defined as both character and numeric.
 115        by AcctNums;
 116        run;
I'm wondering if there is a way to specify whether it's character or numeric.

Such issues almost always arise when proc import is used for reading data. Using a data step is recommended to prevent such situations. So, if you can: fix the import. If you can't do that, you will have to process one of the datasets and convert the numeric version to char.