data merge

[rj]

Hi All, 

I have two datasets which which have one column common to both the datasets.

Problem is that one variable which is common to both the dataset. Variable C is numeric in dataset A  and character in Dataset B, i tried converting numeric to character using input function and then merge the two files into one master file but SAS datastep is not allowing it to convert it to character and keeps the variable numeric and merge fails because of that.

The merge can only happen if this variable C is character in both the datasets.

Anyone who faced similar problem, please share any suggestions that can help me merge dataset A and dataset B on variable C.

Tried using Proc sql too didn't work.

Any help is appreciated!

Thanks in advance!

RJ

[art297]

/*set age to character*/
data probably_have (drop=_age);
  set sashelp.class (rename=(age=_age));
  age=put(_age,$2.);
run;

/*set age to from character to numeric*/
data want (drop=_age);
  set probably_have (rename=(age=_age));
  age=input(_age,8.);
run;

You can only change a variable's type (and keep its name) by renaming the old variable.

Art, CEO, AnalystFinder.com

[rj]

I tried it but SAS UE is forcing the variable to remain numeric, and I tried using SQL too when i do data merge it doesn't give me any rows but only columns

[art297]

Post the code you tried.

Art, CEO, AnalystFinder.com

[LinusH]

You can solve this in single step by using SQL join instead. It will work but you need to use the put function to convert to char.

[rj]

Now even though I changed the variable to character , I'm not able to merge, codes run fine but I am not getting anything in results section, no rows only columns are displayed in results window.

What can be the reason of no rows but only columns in output?
codes are below
DATA jan.masterdata5;
MERGE jan.summtrd09 (IN=A drop= _type_ _freq_ yr wk)
Jan.summdtc1 (IN=B drop = _type_ _freq_ yr wk);
BY DMA_1 week_nm ;
IF A AND B THEN OUTPUT jan.masterdata5;
RUN;

[art297]

Post a copy of your log.

Art, CEO, AnalystFinder.com

[rj]

Log isn't showing any error. I don't understand why the codes are running fine, the data step is even merging the columns but there is no data in the columns

[rj]

Now that i have tried another join in data merge I am getting an error.
codes:
DATA Jan.masterdata11;
MERGE Jan.summtrd09 (IN=A )
Jan.summdtc1 (IN=B );
BY DMA_1 week_nm ;
IF a or b then OUTPUT= Jan.masterdata11;
RUN;

log :
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
55
56 DATA Jan.masterdata11;
57 MERGE Jan.summtrd09 (IN=A )
58 Jan.summdtc1 (IN=B );
59 BY DMA_1 week_nm ;
60 IF a or b then OUTPUT= Jan.masterdata11;
________________
557
ERROR: DATA STEP Component Object failure. Aborted during the COMPILATION phase.
ERROR 557-185: Variable Jan is not an object.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.02 seconds

[art297]

Another thought, as I had understood you were changing the one variable to also be numeric. That would be a lot easier as, using character variables, both character variables have to be the same length.

Art, CEO, AnalystFinder.com

[rj]

The variables I changed are now of same length.

[art297]

change the line:

IF a or b then OUTPUT= Jan.masterdata11;

to: IF a or b;

You could just get rid of the equal sign, but including output jan.masterdata11 would be redundant since you're only outputting one dataset.

Art, CEO, AnalystFinder.com

[rj]

let me try .

[rj]

if I run the code below :
DATA Jan.masterdata11;
MERGE Jan.summtrd09 (IN=A )
Jan.summdtc1 (IN=B );
BY DMA_1 week_nm ;
IF A or B;
RUN;

then I can see all the columns in my dataset but then I have values missing in most of the columns.

[rj]

I checked both Jan.summtrd09 and Jan.summdtc1 before merging there are no missing data there. please advise why the merge is not successful