topic Re: Row by Row operation in a dataset in New SAS User

Row by Row operation in a dataset

bhu — Mon, 11 Feb 2019 22:32:10 GMT

I have a dataset ""table_names which contains table names / dataset names.

I want to take each table name at a time and count the number of rows in that table and display.

Here is the example of my table.

DATA table_names;
length name $32 ;
input name;
DATALINES ;
tablea
tableb
tablec
;
RUN;

I want result like this assuming that tablea has 30 rows, tableb has 45 rows and tablec has 50 rows:

name rowcount

tablea 30

tableb 45

tablec 50

Re: Row by Row operation in a dataset

Reeza — Mon, 11 Feb 2019 23:01:44 GMT

Are these SAS data sets? Are they in a single library or multilple libraries?

There are a set of tables, dictionary tables, that hold this type of data so it's super easy to query that table. The table for this would be sashelp.vtable.

Re: Row by Row operation in a dataset

novinosrin — Mon, 11 Feb 2019 23:03:08 GMT


data tablea tableb tablec;
x='blahblah';
run;




DATA table_names;
length name $32 ;
input name;
DATALINES ;
tablea
tableb
tablec
;
RUN;


data want;
if _n_=1 then do;
if 0 then set sashelp.vtable(keep=memname nobs);
   dcl hash H (dataset:"sashelp.vtable(keep=libname memname nobs where=(libname='WORK'))") ;
   h.definekey  ("memname") ;
   h.definedata ("memname","nobs") ;
   h.definedone () ;
   end;
set table_names;
if h.find(key:upcase(name))=0;
run;

Re: Row by Row operation in a dataset

bhu — Mon, 11 Feb 2019 23:07:50 GMT

The datasets are not SAS datasets, they are sql tables. I am accessing sql tables through sas and counting them.

Re: Row by Row operation in a dataset

novinosrin — Mon, 11 Feb 2019 23:11:30 GMT

with SAS access lib-name engine, you can treat the db tables as datasets and play with hashes

Re: Row by Row operation in a dataset

Reeza — Mon, 11 Feb 2019 23:23:07 GMT

Which DB? Can you hit their dictionary tables? Otherwise, the solution is to write a small macro that counts each file and appends it to a dataset. You can then use CALL EXECUTE to run the macro multiple times.

Re: Row by Row operation in a dataset

ballardw — Mon, 11 Feb 2019 23:23:26 GMT

@bhu wrote:

The datasets are not SAS datasets, they are sql tables. I am accessing sql tables through sas and counting them.

Do you have SAS/Access to use those tables?

Many DBMS have their own set of tables containing meta data and you may be able to query the source database(s) about the properties of data tables.

Re: Row by Row operation in a dataset

Reeza — Mon, 11 Feb 2019 23:30:45 GMT

This can get you started. You'll likely need to make changes to account for a library reference or change how the names are passed.

1. Create a macro that counts the number of records in a single data set

2. Append that into master data set

3. Drop temporary tables

4. Run for each table in list

Note that this will create it into a table called myRecs. You can customize that name.

*macro to count the number of records;
%macro countRecs(tableName= , dset=);

proc sql noprint;
create table _temp as
select "&tableName" as tableName length=32, count(*) as nRecs
from &tableName;
quit;

proc append base=&dset data=_temp;
run;

%*drop table to avoid errors;
proc sql noprint;
drop table _temp;
quit;

%mend;

*data set with list of table names;
data demo;
input tableName $32.;
cards;
sashelp.class
sashelp.cars
sashelp.heart
;
run;

*run macro for each data set name;
data run;
set demo;

*creates string that looks like macro call;
str = catt('%countRecs(tableName=', tableName, ', dset=Myrecs);');
call execute(str);

run;

@bhu wrote:

I have a dataset ""table_names which contains table names / dataset names.

I want to take each table name at a time and count the number of rows in that table and display.

Here is the example of my table.

DATA table_names;
length name $32 ;
input name;
DATALINES ;
tablea
tableb
tablec
;
RUN;

I want result like this assuming that tablea has 30 rows, tableb has 45 rows and tablec has 50 rows:

name rowcount

tablea 30

tableb 45

tablec 50

Re: Row by Row operation in a dataset

bhu — Mon, 11 Feb 2019 23:33:15 GMT

This works fine for sas datasets inside the WORK library. Changing the libname to SQL, shows 0 rows for all tables in sashelp.vtable.

Re: Row by Row operation in a dataset

bhu — Tue, 12 Feb 2019 00:29:42 GMT

Thank you all for replying and get the solution.

Re: Row by Row operation in a dataset

MichaelLarsen — Tue, 12 Feb 2019 14:01:56 GMT

This is not because the accepted solution is not fine.

I just found an opportunity to try out a perfect example of using the DOSUBL function

%macro countobs(table);
  %global rows;
  %let rows=0;
  proc sql noprint;
    select count(*) format=12. into : rows trimmed
    from &table. ;
  quit;
%mend;

data tables;
  length table $41;
  input table;
  rc = dosubl(catt('%countobs(', table, ' )') );
  rows = input(symget('rows'),12.);
  drop rc;
  cards;
sashelp.class
sashelp.cars
;
run;