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))
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.