topic Re: combine multiple tables(>10) into one by using macro in SAS Programming

combine multiple tables(>10) into one by using macro

Ivan555 — Thu, 30 May 2019 16:49:56 GMT

Hello!

I have a goal to craft one table as an output of a several ones. The quantity of tables being combined may differ(in example i've put 2).

I am thinking already rather much time, but i still cant catch an idea how could I to match all tables into one...

If anyone can help, i'll be very grateful.

(version SAS 9.4)

data input_dates;
length dt $9;
input dt;
dtnum +1;
datalines;
01JAN1950
01JUL1950
;
run;

proc sql noprint; select count(*) into: dtcnt from input_dates; quit;

%macro aaa;
%do i=1 %to &dtcnt;
proc sql noprint; select dt into: rdate from input_dates where dtnum = &dtcnt; quit;

proc sql;
    create table a1 as
    select date, air
    from sashelp.airline
    where date < "&rdate."d
    order by date desc;

    create table a11 as
    select date, air
    from a1(firstobs=1 obs=1);
quit;

data want;
merge a11;  /*i want here(or mabe in another place if need) to merge somehow all the tables with names a11*/
run;

%end
%mend aaa

%aaa

TY!

Re: combine multiple tables(>10) into one by using macro

Panagiotis — Fri, 31 May 2019 15:04:12 GMT

To confirm. You have a few tables that you would like to pull and merge together to create one table and you won't know how many tables are going to be merged?

You should look into dictionary.tables. If they are in the same library or having a specific naming convention you can find them and store the names in a macro variable.

https://go.documentation.sas.com/?docsetId=sqlproc&docsetTarget=n02s19q65mw08gn140bwfdh7spx7.htm&docsetVersion=9.4&locale=en

Here is a program that could work, but I have made some assumptions:

- All tables have a specific naming convention

- All tables have the same name column to merge on

/*Create the tables*/
data input_dates1;
length dt $9;
input dt;
dtnum1 +1;
value1=1111;
datalines;
01JAN1950
01JUL1950
;
run;

data input_dates2;
length dt $9;
input dt;
dtnum2 +1;
value2=22222;
datalines;
01JAN1950
01JUL1950
;
run;

data input_dates3;
length dt $9;
input dt;
dtnum3 +1;
value3=33333;
datalines;
01JAN1950
01JUL1950
;
run;

/*Use dictionary.tables to find specific tables. View all here*/
proc sql inobs=50;
select *
	from dictionary.tables;
quit;

/*Create a list the tables you would like and store in macro. I make the assumption they all start with 
INPUT_DATES and end with a number. You could also place all the tables in one specific library and remove memname=.*/
proc sql; /*Add noprint after validated*/ 
select memname
    into :tables separated by " "
from dictionary.tables
where libname="WORK" and memname like "INPUT_DATES_"; 
quit;
%put &=tables; /*View the list of tables I want*/


data want;
   merge &tables;
   by dt;
run;


/************************************/
/*Code from above in a macro program*/
/************************************/
%macro aaa(lib,tableprefix);
%let lib=%upcase(&lib);
%let tableprefix=%upcase(&tableprefix);

proc sql noprint; 
select memname
    into :tables separated by " "
from dictionary.tables
where libname="&lib" and memname like "&tableprefix"; 
quit;
%put &=tables;

data want;
   merge &tables;
   by dt;
run;

%mend aaa;

%aaa(work,input_dates_)

Re: combine multiple tables(>10) into one by using macro

Tom — Fri, 31 May 2019 15:19:31 GMT

Your program example doesn't match your description. You talk about combining multiple tables, but in your example you just seem to be pulling different values from one table.

Re: combine multiple tables(>10) into one by using macro

Ivan555 — Mon, 03 Jun 2019 10:56:18 GMT

Fist of all sorry, seems I've done multiposting, but that wasn't done on purpose, just maybe in case of a long moderation time..

(another post: https://communities.sas.com/t5/SAS-Programming/combining-multiple-tables-qty-various-in-one-dataset-table-by/m-p/562778#M157698 )

@Tom @Panagiotis

About the subject: as I see it - I have dates table which in example has 2 rows, but in life can have 10-50 rows or more.

For each datevalue I thought to make a single output table. After, I thought to make one huge table being base on the output ones.

Finally, after thinking, I've reached a result which I expected(solvation for me was in proc append), but maybe it is not the most optimal way - I am rather new both in SAS and SQL, so I can't judge about it..

Code: (output_date = want)

data input_dates;
length dt $9;
input dt;
dtnum +1;
datalines;
01JAN1950
01JUL1950
;
run;

data output_dates;
input DATE;
format DATE date9.;
input AIR;
format AIR int;
datalines;
;

proc sql noprint; select count(*) into: dtcnt from input_dates; quit;

%macro aaa;
%do i=1 %to &dtcnt;
proc sql noprint; select dt into: rdate from input_dates where dtnum = &i; quit;

proc sql;
    create table a1 as
    select date, air
    from sashelp.airline
    where date < "&rdate."d
    order by date desc;

    create table a11 as
    select date, air
    from a1(firstobs=1 obs=1);
quit;

proc append base = output_dates data = a11;
run;

%end
%mend aaa

%aaa