topic Re: Concatenate multiple tables - macro loop in SAS Programming

Concatenate multiple tables - macro loop

MargoBlue — Fri, 14 Feb 2020 15:06:35 GMT

I want to concatenate tables, which names look like: tab_yyyymm

I write macro:

%macro pol_kol(data_p,period);
data tab_all;
set
	%do i=1 %to %sysevalf(&period);
			%let data_n = %sysfunc(inputn(&data_p,yymmn6.));
			%let data= %sysfunc(intnx(month,&data_n,&i),yymmn6.);
			tab_&data.
	%end;
;
by a b;
run;
%mend;

%pol_kol(201812,14);

And it works.

I want add a condition, which depends on actual date.

If I make:%pol_kol(201812,25), there will be a mistake.

The last table which is now available is tab_202001 (it appeared about 5thFeb-10thFeb) and of course there are previous ones (tab_201912, tab_201911 etc.). Every month the new table appears.

I try add condition and loop:

%let month = %sysfunc(month(%sysfunc(inputn(&sysdate,yymmn6.))));

and similar one for a year.

%do %until(&data<= %eval((%eval(&year*100))+(%eval(&month-1))));

tab_&data.

%end;

But it doesn't work.

Could you tell me where is a mistake and what I should correct?

Re: Concatenate multiple tables - macro loop

ballardw — Fri, 14 Feb 2020 15:14:08 GMT

There may not be any need for a macro loop if your sets follow a standard naming convention as this appears to use.

The SET statement will accept LISTS.

Example

data want;

set lib.dataxn: ;

would concatenate all the data sets in the given library whose names start with DATAXN that is what the colon indicates.

Or you can specify a range:

data want;

set lib.data20190101 - lib.data20190515;

Assuming that the two named sets exist then you would get those two plus any of the names that occur between. Caution: short names might get included.

So to combine sets of a given year:

data want;

set tab2019: ;

would get all of your current 2019 sets.

And multiple years could be

data want;

set tab2018: tab2019: tab2020: ;

Re: Concatenate multiple tables - macro loop

Tom — Fri, 14 Feb 2020 15:19:38 GMT

Sounds like you just want to check if the table exists before including it. Also move the constant code out of the loop.

%macro pol_kol(data_p,period);
%local i date_n dataset ;
%let data_n = %sysfunc(inputn(&data_p,yymmn6.));

data tab_all;
  set
%do i=1 %to %sysevalf(&period);
  %let dataset = tab_%sysfunc(intnx(month,&data_n,&i),yymmn6.);
  %if %sysfunc(exist(&dataset)) %then &dataset ;
%end;
  ;
  by a b;
run;
%mend pol_kol;

%pol_kol(data_p=201812,period=25)