BookmarkSubscribeRSS Feed
rj
Obsidian | Level 7 rj
Obsidian | Level 7

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

21 REPLIES 21
art297
Opal | Level 21
/*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
Obsidian | Level 7 rj
Obsidian | Level 7
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
Opal | Level 21

Post the code you tried.

 

Art, CEO, AnalystFinder.com

 

LinusH
Tourmaline | Level 20
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
rj
Obsidian | Level 7 rj
Obsidian | Level 7
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
Opal | Level 21

Post a copy of your log.

 

Art, CEO, AnalystFinder.com

 

rj
Obsidian | Level 7 rj
Obsidian | Level 7
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
Obsidian | Level 7 rj
Obsidian | Level 7
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
Opal | Level 21

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
Obsidian | Level 7 rj
Obsidian | Level 7
The variables I changed are now of same length.
art297
Opal | Level 21

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
Obsidian | Level 7 rj
Obsidian | Level 7
let me try .
rj
Obsidian | Level 7 rj
Obsidian | Level 7
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
Obsidian | Level 7 rj
Obsidian | Level 7
I checked both Jan.summtrd09 and Jan.summdtc1 before merging there are no missing data there. please advise why the merge is not successful

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 21 replies
  • 1605 views
  • 2 likes
  • 4 in conversation