DATA Step, Macro, Functions and more

using macro to create view for split dataset

Reply
Occasional Contributor
Posts: 5

using macro to create view for split dataset

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.

Regular Contributor
Posts: 156

Re: using macro to create view for split dataset

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). 

Occasional Contributor
Posts: 5

Re: using macro to create view for split dataset

thx for PhilC,

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

Super User
Super User
Posts: 7,392

Re: using macro to create view for split dataset

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;

Regular Contributor
Posts: 156

Re: using macro to create view for split dataset

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;
Super User
Posts: 17,750

Re: using macro to create view for split dataset

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

Data ABC/view=ABC;

set ABC:;

run;

Regular Contributor
Posts: 156

Re: using macro to create view for split dataset

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

Super User
Posts: 17,750

Re: using macro to create view for split dataset

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.

Regular Contributor
Posts: 156

Re: using macro to create view for split dataset

ok, it could be that simple.

Occasional Contributor
Posts: 5

Re: using macro to create view for split dataset

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

Contributor ndp
Contributor
Posts: 61

Re: using macro to create view for split dataset

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

Super User
Posts: 9,662

Re: using macro to create view for split dataset

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

Ask a Question
Discussion stats
  • 11 replies
  • 362 views
  • 0 likes
  • 6 in conversation