Hello all,
i've been struggling with the following...
I have 15 tables (all stored in a SAS Folder). Each table name starts with TRNSCTN followed by a transaction number (i.e. TRNSCTN300, TRNSCTN354, TRNSCTN780, etc.).
each transaction table has a different amount of column numbers, with different column names. however, some of these columns represent the same values. For example:
TRNSCTN300 - Column names: Customer_ID, Order, Time
TRNSCTN354 - Column names: Customer ID, Order number, time of day
This is the case for all 15 tables. Sometimes the column names do match in some tables and not in others.
My question is, how can I go about appending the columns that require combining into 1 column in a master dataset, from those 15 tables? I will need to do this for about 15-20 columns as well...
I tried using a macro to help loop through the tables and concatenate the column names "Customer_ID" and "Customer ID" that are found across all 15 tables, however it ran so many errors...
I have attached my code below.
%macro concatenate_columns;
LIBNAME mylib "XXXXXXX";
/* Step 2: Create an empty master dataset with the concatenated column */ data mylib.master_dataset;
length concatenated_column $200; /* Define the length of the new column as needed */
format concatenated_column $CHAR3.; /* Apply CHAR3. format to allow for three letters */ run;
%do i = 1 %to 15; /* Loop through the 15 tables */
/* Generate code for concatenating columns from different tables */
data temp_table;
set mylib.TRNSCTN&i(keep=Customer_ID, Customer ID);
concatenated_column = catx(' ', coalesce(trim(Customer_ID), trim(Customer_ID))); /* Concatenate values handling variations in column names */
drop Customer_ID Customer ID;
run;
/* Append to the master dataset */
data mylib.master_dataset;
set mylib.master_dataset (in=a);
if a then output; /* Copy existing data from master_dataset */
set temp_table (keep=concatenated_column);
run;
%end;
%mend concatenate_columns;
/* Step 4: Execute the macro */
%concatenate_columns;
I get many issues, including:
- File MYLIB.TRNSCTN.DATA does not exist.
- Undeclared array referenced: i.
- Variable concatenated_column has been defined as both character and numeric.
- The %END statement is not valid in open code.
Is there a more efficient or effective approach to this?
Thanks
... View more
