Hi everybody What I have and I can do Here's my issue : I would like to merge several tables, which are called tab_X. X is a number. In each table, there are two columns A and B with the same content, except B has X removed rows. For instance, here is tab_3 : tab_3 +----+---+---+
| id | A | B |
+----+---+---+
| 1 | a | a |
| 2 | b | b |
| 3 | c | c |
| 4 | d | d |
| 5 | e | e |
| 6 | f | f | | 7 | g | |
| 8 | h | | | 9 | i | |
+----+---+---+ Now, I have a macro that can generate any number of such tables as I want. This means if I execute : %table_maker (removed_rows = 3 5 ) What I get are two distinct tables, which are "tab_3" and "tab_5". What I want to do Now, for further analysis of my Data, I would like that every column B I ever produce are compared to column A in a single table. This means I would like to merge "tab_3" and "tab_5" so that I get : tab_all +----+---+----+----+
| id | A | B3 | B5 |
+----+---+----+----+
| 1 | a | a | a |
| 2 | b | b | b |
| 3 | c | c | c |
| 4 | d | d | d |
| 5 | e | e | |
| 6 | f | f | | | 7 | g | | |
| 8 | h | | | | 9 | i | | |
+----+---+----+----+ Which would thus involve two tricks : 1) I am merging tab_3 with tab_5 to create a new table (but more generaly, I would like to merge any number of tables I get) 2) I am renaming each B column so that columns with same variable names dont erase each other (preferably in a "B_X" fashion where X is the suffix of my table) What I have attempted : first code I tried So far, what I have try is this : %macro table_maker (removed_rows =) ;
/* precedent code in the macro succesfully creates tables */
/* following code is to merge everything with renaming of columns */
/* we create local macro-variables for the loop */
%LOCAL index number_of_tables ;
%LET number_of_tables = %sysfunc(countw(&removed_rows.)) ;
/* we perform the loop itself */
%DO index=1 %TO &number_of_tables. ;
%LET i = %scan(&removed_rows.,&i.) ;
DATA lib.tab_all ;
RENAME B=B_&i. ;
MERGE tab_&i. ;
RUN ;
%END ;
%mend
This code does not work, and what I get in the end is : tab_all (wrong result) +----+---+----+
| id | A | B5 |
+----+---+----+
| 1 | a | a |
| 2 | b | b |
| 3 | c | c |
| 4 | d | d |
| 5 | e | |
| 6 | f | | | 7 | g | |
| 8 | h | | | 9 | i | |
+----+---+----+ It seems that the last table I merge (here, tab_5) erases the former tables that have been merged before (here tab_3) What I have attempted : a 2nd code testing I have try also another way, with SET instead of MERGE : /* we perform the loop itself */
%DO index=1 %TO &number_of_tables. ;
%LET i = %scan(&removed_rows.,&i.) ;
DATA lib.tab_all ;
RENAME B=B_&i. ;
SET tab_&i. ;
RUN ;
%END ; But again, what I get is not the union of every table. It keeps only the columns of the last table. What should I correct in my code so that each table I created merge into a single table with renamed columns ? I work under SAS Enterprise Guide, and my version of SAS is SAS 9.1.3 Thanks in advance ! Any help would be greatly appreciated.
... View more
