topic Re: data merge in SAS Programming

data merge

rj — Sat, 04 Mar 2017 02:25:53 GMT

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!

Re: data merge

art297 — Sat, 04 Mar 2017 02:35:27 GMT

/*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

Re: data merge

LinusH — Sat, 04 Mar 2017 11:04:02 GMT

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.

Re: data merge

rj — Sat, 04 Mar 2017 21:27:28 GMT

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

Re: data merge

art297 — Sat, 04 Mar 2017 21:30:26 GMT

Post the code you tried.

Art, CEO, AnalystFinder.com

Re: data merge

rj — Sun, 05 Mar 2017 19:04:39 GMT

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;

Re: data merge

art297 — Sun, 05 Mar 2017 19:36:41 GMT

Post a copy of your log.

Art, CEO, AnalystFinder.com

Re: data merge

art297 — Sun, 05 Mar 2017 19:41:25 GMT

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

Re: data merge

rj — Sun, 05 Mar 2017 19:51:41 GMT

The variables I changed are now of same length.

Re: data merge

rj — Sun, 05 Mar 2017 19:53:14 GMT

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

Re: data merge

rj — Sun, 05 Mar 2017 19:58:27 GMT

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

Re: data merge

art297 — Sun, 05 Mar 2017 20:04:15 GMT

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

Re: data merge

rj — Sun, 05 Mar 2017 20:07:03 GMT

let me try .

Re: data merge

rj — Sun, 05 Mar 2017 20:10:27 GMT

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.

Re: data merge

rj — Sun, 05 Mar 2017 20:13:05 GMT

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

Re: data merge

art297 — Sun, 05 Mar 2017 20:18:11 GMT

At some point you changed your selection criteria from

A and B to A or B

Which do you want?

As far as why you are getting a lot of missing values, other than the above reason, you would have to post example data.

Art, CEO, AnalystFinder.com

Re: data merge

rj — Sun, 05 Mar 2017 20:21:08 GMT

I want a and b but i thought maybe changing selection criteria might help in merge.Thanks!

Re: data merge

rj — Sun, 05 Mar 2017 20:29:42 GMT

Before merging I can see all the data in two files that I am trying to merge. But after merge I don't understand why I am not getting data in output for some of the columns.

Re: data merge

art297 — Sun, 05 Mar 2017 20:52:16 GMT

Can't help you without seeing examples of your two datasets and, based on the examples, what you expect to end up with.

Art, CEO, AnalystFinder.com

Re: data merge

rj — Mon, 06 Mar 2017 18:02:23 GMT

Hi,

It seems like there was a problem in raw file, even though the common key of two datasets was converted to character, the length of that variable in raw file was different and due to that merge failed.

even though the SAS codes were correct it was actually length of character variable that didn't help merge two datasets.

Thanks!