topic Re: dynamic list of tables in a view definition in SAS Programming

dynamic list of tables in a view definition

Lucas — Fri, 23 Jan 2015 16:31:47 GMT

Hi,

I'm trying to create a view with parameterized list of tables. View definition must be create once and must be automatically refreshed.

List of tables is keep in a data set which can be edit by the user.

I was trying to create a macro in autocall library and use it in view definition. Like this:

%macro table;

sashelp.class sashelp.class

%mend;

data v_table / view=v_table;

set %table;

run;

but I don't know how to create table list based on data set:

data tables;

input table_nm $32.;

datalines;

sashelp.class

;

%macro table;

%mend;

data v_table / view=v_table;

set %table;

run;

Maybe there is an easier way ?

Re: dynamic list of tables in a view definition

data_null__ — Fri, 23 Jan 2015 16:56:01 GMT

How about DOSUBL? You can also make %TABLE have a parameter to subset the list of table names.

proc datasets kill; 
   run; 

data tables;
   input table_nm $32.; 
   datalines; 
sashelp.class
sashelp.class
; 
  
%macro table; 
   %local x rc tablelist;
   %let x=%str(proc sql; select table_nm into :tablelist separated by ' ' from tables; quit; run;); 
   %let rc=%sysfunc(dosubl(&x));
   &tablelist
%mend; 

data v_table / view=v_table;
   set %table; 
   run; 

proc print data=v_table;
   run;

Re: dynamic list of tables in a view definition

ballardw — Fri, 23 Jan 2015 17:16:51 GMT

I'm not sure what you are meaning by a "table list" in this instance. Please provide a bit more detail.

Are you attempting to create a view that automatically updates the description of the data view from the descriptions in the tables dataset so that the view definition changes when items are added to the table without rerunning the view descriptor?

I don't think that would work as the datasets described at the time the descriptor code (the data v_table / view=v_table;)

is what will be in the view regardless of changes to the values in the tables data set. You would have to do something like

%macro table;

proc sql noprint;

select table_nm into : tablelist separated by ' '

from tables

;

quit;

data v_table / view=v_table;

set &tablelist;

run;

%mend;

And use

%table;

To make this more general you would add parameters to have 1) the table to read for the list, 2) the name of the output view and 3) possibly the variable name in the table to use.

Re: dynamic list of tables in a view definition

Tom — Fri, 23 Jan 2015 18:27:54 GMT

If all of the possible tables exist and you just want to pass in which ones to read then you could do it with a data step view. But all of the tables need to exist when the view is defined and when it is run. (see below) But you are probably better off using macro logic (or macro variable) to define the view/dataset based on the list from the table at run time.

data table1 table2 table3 ;

set sashelp.class ;

run;

data my_tables (index=(dsname));

input dsname $41. ;

dsname=upcase(dsname);

cards;

work.table1

work.table2

;;;;

data all / view=all ;

length dsname dsname2 $41 ;

set work.table: indsname=dsname2 ;

dsname=dsname2;

set my_tables key=dsname /unique ;

if dsname2 ne lag(dsname2) then put dsname2= _n_= obsnum= _iorc_=;

if not _iorc_ then do; obsnum+1; output; end;

_error_=0;

_iorc_=0;

run;

data _null_;

if eof then put (_n_ obsnum) (=);

set all end=eof ;

run;

Re: dynamic list of tables in a view definition

Lucas — Mon, 26 Jan 2015 20:14:17 GMT

Thank you for all responses. I have read more about views and I think there is no way to achieve what I want.

I will provide more details about my problem. I think now it will be easier to understand.

There is a process that creates a table once a moth:

INCOMES_201410

INCOMES_201411

INCOMES_201412

INCOMES_201501

INCOMES_201502

I need to create a view which provides data from all tables INCOMES_*, but I don't want to change definition of the view each month.

I was hoping there is a way to create a view once and when someone use this view it automatically check which tables to use.

Re: dynamic list of tables in a view definition

ballardw — Mon, 26 Jan 2015 20:48:39 GMT

You could create your own batch job that checks daily, or as appropriate, to see if that file has been updated and then run a SAS job whose only purpose is to update those definitions. Ideal would be to piggy-back on the other process to call the SAS job at the end but I realize that may not be practical.

Re: dynamic list of tables in a view definition

Tom — Mon, 26 Jan 2015 22:49:53 GMT

Why?

1) Why do you want a view? Why not just have a macro or some other piece of dynamic code?

2) Why NOT just re-define the view? You are already running a program to create the new dataset. It is a trivial extra step to have that program define the view.

Re: dynamic list of tables in a view definition

Jbabu — Fri, 03 Jul 2015 06:22:36 GMT

i have list of varibles how to make create macros list

_0 _1 _2 to _200

how can i make this list in macros because its included '_' its getting confustion to create list

and again i have convert to char to numeric :

Proc sql ;

select name into :vars separated by ' ' from sample;

quit;

in above code how can i declare a range instead of using name what can i declare my numbers range can any one clarify on this

%let Novars =%sysfunc(countw(&vars)) ;

%do i = 1 % to &Novars ;

%local &&Ovar&i ;

%let Ovar&i = %scan(&vars , &i ) ;

//%let Finavar&i = "_"||strip(&&Ovar&i) ;

%end l

data sample 1;

set sample ;

%do i = 1 % 19 ;

&&var&i = input(&&Finavar&i,best12.);

%end;

drop _000-_019;

run;

Re: dynamic list of tables in a view definition

Tom — Fri, 03 Jul 2015 15:17:54 GMT

Please make a new question thread for this as it does not seem to be related to the original question.