BookmarkSubscribeRSS Feed
fabdu92
Obsidian | Level 7

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.

 

4 REPLIES 4
Astounding
PROC Star

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.

Shmuel
Garnet | Level 18

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;

fabdu92
Obsidian | Level 7

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.) 
Shmuel
Garnet | Level 18

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

   %sysfunc(exist(&outtabel))

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 4 replies
  • 988 views
  • 2 likes
  • 3 in conversation