Hi,

Thanks for any tips:

I have over 400 daily tbls on Unix.. Majority of these tables have
identical fields and types (char/num)
Just a few of them have slightly different fields due to changes in
production.. but i am not sure what dates it started from.

I built a marco to store all the daily tables' names.. and try to use
proc append.. FORCE option to append them.. ( SET in data step doesn't
work because of the dicrepancy among the tables, that is why I think
the FORCE option in proc append may help). I thought i was smart..
unfortunately, I got stuck..
Seems proc append can only combine two tables at a time.

rsubmit;
proc sql ;
	Select table_names into : tbl_nm separated by '  '
    from all_table_names;
quit;

proc append data=base_table data=&tbl_nm  force;
run;

endrsubmit;

Wendy

On Jan 19, 6:16=A0pm, pigpigpig <pigzhu...@gmail.com> wrote:
> Hi,
>
> Thanks for any tips:
>
> I have over 400 daily tbls on Unix.. Majority of these tables have
> identical fields and types (char/num)
> Just a few of them have slightly different fields due to changes in
> production.. but i am not sure what dates it started from.
>
> I built a marco to store all the daily tables' names.. and try to use
> proc append.. FORCE option to append them.. ( SET in data step doesn't
> work because of the dicrepancy among the tables, that is why I think
> the FORCE option in proc append may help). I thought i was smart..
> unfortunately, I got stuck..
> Seems proc append can only combine two tables at a time.
>
> rsubmit;
> proc sql ;
> =A0 =A0 =A0 =A0 Select table_names into : tbl_nm separated by ' =A0'
> =A0 =A0 from all_table_names;
> quit;
>
> proc append data=3Dbase_table data=3D&tbl_nm =A0force;
> run;
>
> endrsubmit;
>
> Wendy

Note that my tables are all on unix.. not local

Hi Wendy,

How about writing a loop over proc append.Some thing like this inside
your macro code.

Hope this helps...Try this...


proc sql ;
    select table_names into :var1 - :var400 from all_table_names;
   select count(table_names) into :cnt from all_table_names;
quit;
%do i=1 to &cnt;
%do table=var&i;

proc append base=base_table data=&table  force;
run;
%end;
%end;

Thanks,
shiva

Summary: Concatenate vs PROC APPEND
#iw-value=1

Wendy,

You may want:

 > rsubmit;
 > proc sql ;
 >         Select table_names into : tbl_nm separated by '  '
 >     from all_table_names;
 > quit;
 >
  data base_table ;
     set &tbl_nm ;
 > run;
 >
 > endrsubmit;

PROC APPEND uses variables from first data set and drops any variables
not conforming to that first pattern; hence it is doubtful that this is
what you want.  The SET statement will add new variables.  In either
case watch out for a variable where the type changes but the name stays
the same, or the type and name stay the same but the meaning changes.

Ian Whitlock
===============

Date:         Tue, 19 Jan 2010 15:17:35 -0800
From:         pigpigpig <pigzhu740@GMAIL.COM>
Subject:      Re: proc append to combine multiple tables on Unix
Content-Type: text/plain; charset=ISO-8859-1

On Jan 19, 6:16 pm, pigpigpig <pigzhu...@gmail.com> wrote:
 > Hi,
 >
 > Thanks for any tips:
 >
 > I have over 400 daily tbls on Unix.. Majority of these tables have
 > identical fields and types (char/num)
 > Just a few of them have slightly different fields due to changes in
 > production.. but i am not sure what dates it started from.
 >
 > I built a marco to store all the daily tables' names.. and try to use
 > proc append.. FORCE option to append them.. ( SET in data step
doesn't
 > work because of the dicrepancy among the tables, that is why I think
 > the FORCE option in proc append may help). I thought i was smart..
 > unfortunately, I got stuck..
 > Seems proc append can only combine two tables at a time.
 >
 > rsubmit;
 > proc sql ;
 >         Select table_names into : tbl_nm separated by '  '
 >     from all_table_names;
 > quit;
 >
 > proc append data=base_table data=&tbl_nm  force;
 > run;
 >
 > endrsubmit;
 >
 > Wendy

Note that my tables are all on unix.. not local

hi,

this will work for you:


%macro appendn ;
proc sql;
select count(table_names) , table_names
into   :cnt               , :tables_list separated by '/' from
from all_table_names;
quit;

%do i=1 %to &cnt;
%let table=%sysfunc(scan(&tables_list,&i,'/'));
proc append base=base_table data=&table force;
run;
%end;
%mend;

%appendn;


I recommend you to write this before calling your macro
to help you to debugging:

options mprint symbolgen;

Type  'nomprint nosymbolgen' to get them off.


Daniel Fernandez.
Barcelona.

2010/1/22 shiva <shiva.saidala@gmail.com>:
> Hi,
>
> I think there is empty space in macro variable cnt.
>
> Try to store that and resolve it.
>
> %macro test;
> %let cnt1=&&cnt;
> %do i=1 %to &cnt1;
>  %let table=&&var&i;
> proc append base=base_table data=&&table force;
> run;
> %end;
> %mend test;
> %test;
>
> Thanks,
> shiva
>