BookmarkSubscribeRSS Feed
DerekChan
Calcite | Level 5

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.

11 REPLIES 11
PhilC
Rhodochrosite | Level 12

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
Calcite | Level 5

thx for PhilC,

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Rhodochrosite | Level 12

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
Super User

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

Data ABC/view=ABC;

set ABC:;

run;

PhilC
Rhodochrosite | Level 12

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

Reeza
Super User

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
Rhodochrosite | Level 12

ok, it could be that simple.

DerekChan
Calcite | Level 5

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
Quartz | Level 8 ndp
Quartz | Level 8

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

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1225 views
  • 0 likes
  • 6 in conversation