DATA Step, Macro, Functions and more

merge data Table

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

merge data Table

How to merge non-empty tables only? skip empty tables.


Accepted Solutions
Solution
‎02-04-2018 03:21 PM
Esteemed Advisor
Posts: 5,543

Re: merge data Table

In what way are empty datasets causing a problem during your merge operation. In the following example, merging with an empty dataset isn't a problem, except for the presence of an empty column

 

data a;
id=1; x=1; output;
id=1; x=2; output;
run;

/* Empty dataset */
data b;
length id x y 8;
stop;
run;

data c;
id=1; z=3; output;
id=1; z=4; output;
run;

data d;
merge a b c;
by id;
run;

proc print data=d noobs; run;
                                 id    x    y    z

                                  1    1    .    3
                                  1    2    .    4
PG

View solution in original post


All Replies
Solution
‎02-04-2018 03:21 PM
Esteemed Advisor
Posts: 5,543

Re: merge data Table

In what way are empty datasets causing a problem during your merge operation. In the following example, merging with an empty dataset isn't a problem, except for the presence of an empty column

 

data a;
id=1; x=1; output;
id=1; x=2; output;
run;

/* Empty dataset */
data b;
length id x y 8;
stop;
run;

data c;
id=1; z=3; output;
id=1; z=4; output;
run;

data d;
merge a b c;
by id;
run;

proc print data=d noobs; run;
                                 id    x    y    z

                                  1    1    .    3
                                  1    2    .    4
PG
New Contributor
Posts: 3

Re: merge data Table

Thank you, PG. There is no issue to merge empty tables. I just don't want to keep columns from empty tables.

Frequent Contributor
Posts: 109

Re: merge data Table

I have created a simple macro. You can use it.

 

%macro tablesize(tablename=);
	proc sql noprint;
	select count(*) into :cnt from &tablename.;
	quit;
	%if &cnt. ne 0 %then %do;
		%let &tablename.=&tablename.;
	%end;
	%else %do;
		%let &tablename.=;
	%end;
%mend tablesize;

%tablesize(tablename=A);
%tablesize(tablename=B);
%tablesize(tablename=C);

data d;
merge &A. &B. &C.;
by common_var;
run;

/*Which of the table is empty its corresponding macro will be initialized to null 
so they wont be merged in the data step as the macro resoves to null
Remember to call macronames insted of tablenames.
Remember Not to have any macro variable same name as tablenames*/

Please let us know if it worked for you.

New Contributor
Posts: 3

Re: merge data Table

Posted in reply to Satish_Parida
Thank you, Satish_Parida. It should work.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 203 views
  • 0 likes
  • 3 in conversation