using macro to create view for split dataset

[DerekChan]

Dear all,

I encounter a problem how to dynamically create view on the datasets which are split by half-year. It starts from ABC_2011H1, ABC_2011H2, ..., ABC_2015H1, etc.

If today is 2015.05.31, I would like to create view to append from ABC_2011H1 to ABC_2015H1, like the following:

proc sql;

create view ABC as

select * from ABC_2011H1

union all

select * from ABC_2011H2

union all

...

...

select * from ABC_2015H1

;

quit;

I would like to make a macro/do-loop to achieve it. It can join based on date (upper half year/lower half year), not by hard-coding.

Please help.

Thank you.

[PhilC]

The techniques in this article can be applied to SQL views. perhaps ?. : How to append all dataset in one library (https://communities.sas.com/thread/64364). 

[DerekChan]

thx for PhilC,

But the library that contains the target datasets also contains other dataset

[RW9]

Take the code given and then modify to your specific situation:

proc sql;

     select  MEMNAME

     into     :MEMNAMES separated by ' '

     from    DICTIONARY.TABLES

     where  LIBNAME="WORK"

          and substr(MEMNAME,1,3)="ABC";  /* Just update the where clauses here to fit your requirements */

quit;

data want;

     set &MEMNAMES.;

run;

[PhilC]

Works with views too...

proc sql noprint;
  select compress(libname || "." ||memname)
    into :datasets separated by ' '
    from Dictionary.tables
    where libname="SASHELP" AND
      "B"<=memname<"D"; *<-- Conditions on Dataset name go here;
quit;

%put &datasets ;

data ABC/view=ABC;
  set &datasets;
run;

[Reeza]

If the datasets all start with ABC then use a colon in the view

Data ABC/view=ABC;

set ABC:;

run;

[PhilC]

but we'll want to add conditions limiting the dataset that are not with in the time range.

[Reeza]

I do agree this only works if there is a consistent naming convention and you want all datasets with the prefix.

My interpretation was that it was from the start, but that there are other data sets in the same library.

 It starts from ABC_2011H1, ABC_2011H2, ..., ABC_2015H1, etc.

[PhilC]

ok, it could be that simple.

[DerekChan]

thx for both answering

I think it works for this simple situtation, but if I want to constraint it

if today is 2015 Apr 1, i would like to read ABC_2011H2, ABC_2012H1, ..., ABC_2015H1

2011H2 can be viewed as the fixed start, i.e. no ABC_2011H1

It is because i want to reduce the risk that if the library contains new datasets that have the name similar to them

data view = a.

is this statement different to proc sql create view in term of performance

becuase i will use to append huge data set piece along different period

[ndp]

What do H1, H2, ...mean? 1st half, 2nd half of year etc...? you don't have dataset for 2011H1 in the directory?

[Ksharp]

You can make a macro variable to hold them all together.

data temp;
 do i=2011 to year(today())-1;
  do j=1,2;
   tname=cats('work.ABC_',i,'H',j);output;
  end;
 end;
 if month(today()) lt 7 then do;tname=cats('work.ABC_',year(today()),'H1');output;end;
  else do; 
   tname=cats('work.ABC_',year(today()),'H1');output;
   tname=cats('work.ABC_',year(today()),'H2');output;
  end;
 drop i j;
run;
proc sql noprint;
 select catx(' ','select * from',tname) into : list separated by ' union all '
  from temp;
  
 create view xxxx as
  &list ;
quit;

%put &list ;

Xia Keshan