Macro to Append tables

Reply
Regular Contributor
Posts: 212

Macro to Append tables

Hi guys, one mor time i need your help.

Thank for you all before i start.

Issue: I need to make a macro step to create an append table, with tables that the sufix ends with date like this 20150731.

And i need to append only 5 tables before the current date from Date() function.

I've tried the following code, in the sas log i saw that it reads all tables, but it only keep the last one:

DATA TESTE_20150731;

X=1;

RUN;

DATA TESTE_20150730;

X=2;

RUN;

DATA TESTE_20150729;

X=3;

RUN;

DATA TESTE_20150728;

X=4;

RUN;

DATA TESTE_20150727;

X=5;

RUN;

%MACRO LAST_TABLE();

%GLOBAL ANOMES

  DIA

  COUNT

  I

  TAB;

%DO I = 0 %TO 4;

  %LET ANOMES = %SYSFUNC(DATE(),YYMMN.);

  %LET DIA    = %SYSFUNC(DATE(),DAY2. );

  %LET COUNT  = %SYSFUNC(SUM(&ANOMES&DIA.-&I.));

  %PUT &COUNT.;

  DATA _NULL_;

  CALL SYMPUTX("TAB","TESTE_&&COUNT.");

  RUN;

  DATA TESTE_FULL;

     SET &TAB.;

  RUN;

  %PUT &TAB.;

%END;

%MEND;

%LAST_TABLE();

What i need to get my tables appended ?

Tks,

Rodrigo D. Elias

Super User
Posts: 19,851

Re: Macro to Append tables

Posted in reply to DartibaliRodrigo

I'll suggest a different method that doesn't use macro's.

Instead query the sashelp.vtable view to select your table names into a macro variable and then append the tables.

Regular Contributor
Posts: 212

Re: Macro to Append tables

But how will i get only the last 3 tables ?

Last 3 days ?

Super User
Posts: 19,851

Re: Macro to Append tables

Posted in reply to DartibaliRodrigo

Untested:

Here's how I'd modify your code:

%MACRO LAST_TABLE();

%GLOBAL ANOMES

  DIA

  COUNT

  I

  TAB;

proc sql noprint;

drop table teste_full;

run;

%DO I = 0 %TO 4;

  %LET ANOMES = %SYSFUNC(DATE(),YYMMN.);

  %LET DIA    = %SYSFUNC(DATE(),DAY2. );

  %LET COUNT  = %SYSFUNC(SUM(&ANOMES&DIA.-&I.));

  %PUT &COUNT.;

  DATA _NULL_;

  CALL SYMPUTX("TAB","TESTE_&&COUNT.");

  RUN;


PROC APPEND BASE=TESTE_FULL DATA=&TAB;

RUN;


  %PUT &TAB.;

%END;

%MEND;

%LAST_TABLE();


Here's how I'd run it:


proc sql noprint;

select memname into :name1-name9999

from sashelp.vtable

where libname = 'WORK'

and memname like 'TESTE_%'

order by memname descending;

run;


data want;

set &name1 &name2 &name3 &name4 &name5;

run;


There are more elegant ways to actually capture the date but it will sort correctly so this is a quick and dirty way.


Ask a Question
Discussion stats
  • 3 replies
  • 265 views
  • 0 likes
  • 2 in conversation