DATA Step, Macro, Functions and more

data merge

Reply
Contributor rj
Contributor
Posts: 36

data merge

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

PROC Star
Posts: 7,366

Re: data merge

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

 

 

 

Contributor rj
Contributor
Posts: 36

Re: data merge

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
PROC Star
Posts: 7,366

Re: data merge

Post the code you tried.

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 5,260

Re: data merge

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.
Data never sleeps
Contributor rj
Contributor
Posts: 36

Re: data merge

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;
PROC Star
Posts: 7,366

Re: data merge

Post a copy of your log.

 

Art, CEO, AnalystFinder.com

 

Contributor rj
Contributor
Posts: 36

Re: data merge

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
Contributor rj
Contributor
Posts: 36

Re: data merge

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
PROC Star
Posts: 7,366

Re: data merge

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

 

Contributor rj
Contributor
Posts: 36

Re: data merge

The variables I changed are now of same length.
PROC Star
Posts: 7,366

Re: data merge

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

Contributor rj
Contributor
Posts: 36

Re: data merge

let me try .
Contributor rj
Contributor
Posts: 36

Re: data merge

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.
Contributor rj
Contributor
Posts: 36

Re: data merge

I checked both Jan.summtrd09 and Jan.summdtc1 before merging there are no missing data there. please advise why the merge is not successful
Ask a Question
Discussion stats
  • 21 replies
  • 262 views
  • 2 likes
  • 4 in conversation