BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mgorripati
Obsidian | Level 7

 

I am trying to merge two datasets and create a new variable based on if the data exists in both tables.

 

Sample Code :

 

 

DATA output;
merge source(in=a) client(in=b);
by ID;
if a and b then Level='1';
if b and not a then Level='0';
RUN;

 

The code above works , but does not create the new variable.

 

How can i create a new variable during the merge process.

 

I can acheive the goal in multiple steps , but is there a way to acheive the above in single step.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Well, the logic is correct so your new variable, Level, should be created. 

 

Review your log, any drop/keep and output statements.

 

Without the actual code and log I don't know that we can suggest anything further. 

 

This shows that the logic is fine:

data class1;
	set sashelp.class;
	keep name age height;

	if name in ("Alfred" "Jane" "Mary") then
		delete;
run;

data class2;
	set sashelp.class;
	keep name sex weight;

	if name in ("John" "Joyce" "William") then
		delete;
run;

proc sort data=class1;
	by name;

proc sort data=class2;
	by name;

data want;
	merge class1 (in=a) class2(in=b);
	by name;

	if a and b then
		level=1;
	else if a and not b then
		level=0;
	else
		level=-1;
run;

View solution in original post

5 REPLIES 5
Reeza
Super User

What does your log say?

It should have some sort of error/warning because your last line is missing a semicolon.

 

 

if b and not a then Level='0';
RUN;

mgorripati
Obsidian | Level 7

Hi Reez,

 

Thanks for catching the error, It is not actual code . I have typed the code here as an example. 

my actual code with similar logic runs fine but it is failing to create the new variable.

Reeza
Super User

Well, the logic is correct so your new variable, Level, should be created. 

 

Review your log, any drop/keep and output statements.

 

Without the actual code and log I don't know that we can suggest anything further. 

 

This shows that the logic is fine:

data class1;
	set sashelp.class;
	keep name age height;

	if name in ("Alfred" "Jane" "Mary") then
		delete;
run;

data class2;
	set sashelp.class;
	keep name sex weight;

	if name in ("John" "Joyce" "William") then
		delete;
run;

proc sort data=class1;
	by name;

proc sort data=class2;
	by name;

data want;
	merge class1 (in=a) class2(in=b);
	by name;

	if a and b then
		level=1;
	else if a and not b then
		level=0;
	else
		level=-1;
run;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Should work fine, check out the below.  Also note that these binary if statements are far simpler if you use ifc/ifn.

data want;
  merge sashelp.class (in=a) sashelp.class (in=b where=(age > 13));
  by name;
  level=ifc(a and b,"1","0");
run;
Astounding
PROC Star

You have posted working code.  You should expect that A and B will be dropped automatically, but LEVEL will be there in the final data set.

 

Since what you posted is not the actual code you are working with,  you may have to post something closer to the actual code to allow someone else to debug it.

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
  • 5 replies
  • 5469 views
  • 1 like
  • 4 in conversation