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.
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.
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;
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.)
The function is EXIST (not existS) - try change line to:
%sysfunc(exist(&outtabel))
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.