topic Re: Create a merge table in SAS Programming

Create a merge table

fabdu92 — Thu, 17 Aug 2017 11:21:21 GMT

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:

-B2:

y w v
4 5 6
2 9 9

-B3:

-B4:

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:

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.

Re: Create a merge table

Astounding — Thu, 17 Aug 2017 13:22:48 GMT

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.

Re: Create a merge table

Shmuel — Thu, 17 Aug 2017 13:32:35 GMT

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;

Re: Create a merge table

fabdu92 — Thu, 17 Aug 2017 14:09:41 GMT

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.)

Re: Create a merge table

Shmuel — Thu, 17 Aug 2017 14:31:07 GMT

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

%sysfunc(exist(&outtabel))