08-18-2015 10:04 AM
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:
create view ABC as
select * from ABC_2011H1
select * from ABC_2011H2
select * from ABC_2015H1
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.
08-18-2015 10:23 AM
08-18-2015 10:42 AM
Take the code given and then modify to your specific situation:
into :MEMNAMES separated by ' '
and substr(MEMNAME,1,3)="ABC"; /* Just update the where clauses here to fit your requirements */
08-18-2015 10:46 AM
Works with views too...
08-18-2015 11:13 AM
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.
08-20-2015 10:54 AM
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
08-21-2015 12:35 AM
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 ;