topic Re: using macro to create view for split dataset in SAS Programming

using macro to create view for split dataset

DerekChan — Tue, 18 Aug 2015 14:04:21 GMT

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.

Re: using macro to create view for split dataset

PhilC — Tue, 18 Aug 2015 14:23:47 GMT

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

Re: using macro to create view for split dataset

DerekChan — Tue, 18 Aug 2015 14:30:36 GMT

thx for PhilC,

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

Re: using macro to create view for split dataset

RW9 — Tue, 18 Aug 2015 14:42:48 GMT

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;

Re: using macro to create view for split dataset

PhilC — Tue, 18 Aug 2015 14:46:39 GMT

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; 

Re: using macro to create view for split dataset

Reeza — Tue, 18 Aug 2015 15:07:00 GMT

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

Data ABC/view=ABC;

set ABC:;

run;

Re: using macro to create view for split dataset

PhilC — Tue, 18 Aug 2015 15:10:12 GMT

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

Re: using macro to create view for split dataset

Reeza — Tue, 18 Aug 2015 15:13:38 GMT

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.

Re: using macro to create view for split dataset

PhilC — Tue, 18 Aug 2015 15:21:49 GMT

ok, it could be that simple.

Re: using macro to create view for split dataset

DerekChan — Thu, 20 Aug 2015 14:54:50 GMT

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

Re: using macro to create view for split dataset

ndp — Thu, 20 Aug 2015 16:24:44 GMT

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

Re: using macro to create view for split dataset

Ksharp — Fri, 21 Aug 2015 04:35:35 GMT

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