DATA Step, Macro, Functions and more

Create a merge table

Reply
Contributor
Posts: 45

Create a merge table

[ Edited ]

Hi all,

I want to create a function which I am not sure it exists:

 

 

Let say I have n tables, named B1 to Bn with a various number of variables, some of them can be common on several table.

 

I want to create a table A as a merge of all  Bi tables, creating a macro function:

 

 

f(input_table, output_table)

which is doing the following thing:

 

- if output_table doesn't exist, macro creates it as equal to input_table

- if output_table already exists, macro appends the actual state of output_table with input_table:

 

==> For each variabale of output_table, existing in input_table, macro appends like an easy proc appends

==> For each variable of output_table, not existing in input_table, macro leaves values empty

==> For each variable of input_table not existing in output_table, macro add this new variable to output_table, and fill in values only from input_table 

 

Then, I just need to call the function like this:

 

do i = 1 to n;
f(Bi, A);
end;

 

 

 

Exemple:

 

Let say I have four tables (variable names are in first row):

 

- B1:

 

z y x
0 2 5
3 5 8
4 5 6
7 8 1

 

 

-B2:

 

y w v
4 5 6
2 9 9

-B3:

 

 

u t s
0 0 0
1 1 1
2 2 2

-B4:

 

 

z v r
1 5 6
4 4 4
9 8 7

 

 

I call my function:

 

f(B1,A);
f(B2,A);
f(B3,A);
f(B4,A);

 

 

After the first call:

A doesn't exist and is created as B1:

 

z y x
0 2 5
3 5 8
4 5 6
7 8 1

 

 

After the second call:

A is:

 

z y x w v
0 2 5 . .
3 5 8 . .
4 5 6 . .
7 8 1 . .
. 4 . 5 6
. 2 . 9 9

 

 

After the third call:

A is:

 

z y x w v u t s
0 2 5 . . . . .
3 5 8 . . . . .
4 5 6 . . . . .
7 8 1 . . . . .
. 4 . 5 6 . . .
. 2 . 9 9 . . .
. . . . . 0 0 0
. . . . . 1 1 1
. . . . . 2 2 2

 

After the fouth and last call:

A is:

z y x w v u t s r
0 2 5 . . . . . .
3 5 8 . . . . . .
4 5 6 . . . . . .
7 8 1 . . . . . .
. 4 . 5 6 . . . .
. 2 . 9 9 . . . .
. . . . . 0 0 0 .
. . . . . 1 1 1 .
. . . . . 2 2 2 .
1 . . . 5 . . . 6
4 . . . 4 . . . 4
9 . . . 8 . . . 7

I can't find how to write the macro, especially to detect if output_table exists or not. Can you help me doing it?

Thanks.

 

Super User
Posts: 6,543

Re: Create a merge table

The vast majority of your requirements are satisfied by a simple program:

 

data a;

set b1 b2 b3 b4 b5;

run;

 

The only wrinkle is determining whether A already exists, in which case you would want:

 

data a;

set a b1 b2 b3 b4 b5;

run;

 

There are plenty of functions to determine whether a data set exists, and macro language could easily add or remove A from the SET statement.

Trusted Advisor
Posts: 1,827

Re: Create a merge table

[ Edited ]

Why to complicate a simple work ? Just do:

 

data want;

   set B1 B2 B3 ... Bn;

run;

 

or even less coding:

 

data want;

  set B: ;

run;

Contributor
Posts: 45

Re: Create a merge table

Thanks for the answer

 

Before asking I tried to write the function like this:

 

%macro f(
	input_table,
	output_table);

%if %sysfunc(exists(&output_table.)) %then %do;
	data &output_table.; set &output_table. &input_table.;run;
%end;
%else %do;
	data &output_table.; set &input_table.; run;
%end;


%mend f;

But when I call it, I have an error:

ERROR: The EXISTS function referenced in the %SYSFUNC or %QSYSFUNC macro function is not found.

When I delete sysfunc and replace by

%exists(&output_table.)

 I have an error:

ERROR: Required operator not found in expression: %exists(&output_table.) 
Trusted Advisor
Posts: 1,827

Re: Create a merge table

The function is EXIST (not existS) - try change  line to:

   %sysfunc(exist(&outtabel))

Ask a Question
Discussion stats
  • 4 replies
  • 140 views
  • 2 likes
  • 3 in conversation