topic Re: how to create a variable for number of observations from 30 tables in New SAS User

how to create a variable for number of observations from 30 tables

changxuosu — Mon, 14 Jan 2019 16:35:04 GMT

Dear SAS community,

I have a question that needs your wisdom.

I have 30 tables, say Table1,table2,...table 30. All of them have the same data structure and the same list of variables except that the ith table denotes the month i.

Now I need to create a new table X, which is composed of two columns/variables.

The first variable of table X is i, the index for table i; the second variable of table X is the number of observations for table i.

Does any one can point me to any idea/procedure/SAS macro that achieve this?

Thanks in advance!!!

Re: how to create a variable for number of observations from 30 tables

Kurt_Bremser — Mon, 14 Jan 2019 16:58:00 GMT

You can pull the dataset names and number of observations from dictionary.tables in SQL, or sashelp.vtable in a data step.

Re: how to create a variable for number of observations from 30 tables

Reeza — Mon, 14 Jan 2019 17:00:04 GMT

SASHELP.VTABLE is a table in SAS with metadata about the tables, you just need to filter it for the tables of interest. I would recommend opening the table first (sashelp.vtable) and looking at it, and then customizing your query.

proc sql;
create table myData as
select libname, memname, nobs
from sashelp.vtable
where upcase(memname) like 'TABLE%';
quit;

SASHELP.VCOLUMN has data about each variable in a data set.

Re: how to create a variable for number of observations from 30 tables

ballardw — Mon, 14 Jan 2019 17:00:21 GMT

@changxuosu wrote:

Dear SAS community,

I have a question that needs your wisdom.

I have 30 tables, say Table1,table2,...table 30. All of them have the same data structure and the same list of variables except that the ith table denotes the month i.

Now I need to create a new table X, which is composed of two columns/variables.

The first variable of table X is i, the index for table i; the second variable of table X is the number of observations for table i.

Does any one can point me to any idea/procedure/SAS macro that achieve this?

Thanks in advance!!!

By index do you mean the 1, 2, 3 etc that are suffixes to your table name? Or something related to a data set index?

You can find a lot of information about table, including observations with something similar to

proc sql;
   create table x as
   select *
   from dictionary.tables
   where libname='WORK' and memname like ('TABLE%')
   ;
run;

The libname and memname values in the dictionary tables is stored in all upper case.

If your names are as simple as you indicate and you want the suffix value then you can get that by

proc sql;
   create table x as
   select substr(memname,6),nobs
   from dictionary.tables
   where libname='WORK' and memname like ('TABLE%')
   ;
run;

Obviously you want to reference the library your data sets exist in.

Re: how to create a variable for number of observations from 30 tables

changxuosu — Mon, 14 Jan 2019 19:09:11 GMT

thank you so much, ballardw, it works perfect