topic Re: how to write a macro variable into existing dataset? in SAS Programming

how to write a macro variable into existing dataset?

Ivan555 — Mon, 19 Aug 2019 15:40:14 GMT

Hello!

I've done a code, question inside:

data t_names;
     length table_name $256 table_cnt 8;
     input table_name;
     table_num +1;
     datalines;
AIR
AIRLINE
BIRD
;
run;

proc sql noprint; select count(*) into dataset_cnt from t_names; quit;
%macro aaa;
     %do i=1 %to &dataset_cnt;
          proc sql noprint; select table_name into: tmp1 from t_names where table_num = &i; quit;
          proc sql noprint; select count(*) into: tmp2 from sashelp.&tmp1; quit;
/*probably here I want to put tmp2 variable in the dataset "t_names" in the column "table_cnt" where "table_num"=&i
how is it possible to write? */
     %end;
mend aaa;

%aaa;

THX!

Re: how to write a macro variable into existing dataset?

PaigeMiller — Mon, 19 Aug 2019 17:01:49 GMT

It would really help if you explained what this program is trying to do.

It seems as if you are trying to count the number of observations in each of the three tables, AIR AIRLINE and BIRD. Is that it? And then you want to add this number of observations to the appropriate row of T_NAMES, yielding (I made up the numbers)

AIR         28
AIRLINE     97
BIRD        12

If that's what you are trying to do, don't bother. All of this information is in the dictionary tables, at SASHELP.VTABLE. You don't have to compute it and then add it to a table, it's already in the dictionary table, which is really just a data view that you can use for any purpose.

Re: how to write a macro variable into existing dataset?

Ivan555 — Tue, 20 Aug 2019 07:20:26 GMT

@PaigeMiller ,

Thank you, you are right, the final goal is to make a dictionary table for each library.

SASHELP is a well-done library, it has dictionary tables.. other libraries I have dont have dictionaries.

> 1/ so on the first step I want to calculate the number of observation and to put it in the dictionary table

> 2/ on the next step I will try to calculate the size 10 observations of each table and to put them in the dictinary table

> 3/ on the next step having 10obsSize and TotalObsCount I will try to calculate approximate full size of the table

2 and 3 steps are not the case of this topic, I will think and I hope after I'll be able to do them myself.. but I can't catch how I could technically put data, being contained in macro variable to existing dataset

Re: how to write a macro variable into existing dataset?

Patrick — Tue, 20 Aug 2019 06:57:35 GMT

@Ivan555

"other libraries I have dont have dictionaries"

SAS adds all tables of all libraries that you've got assigned in a SAS session to the SAS dictionary tables. These SAS dictionary tables are also accessible via views under library SASHELP.

ALL SAS tables will have the number of rows as an attribute stored in the dictionary tables. Tables from libraries that point to databases will have a missing value for the row count because that's not a table attribute of database tables (like it is for SAS tables).

If you clearly specify what you want to achieve then I'm sure people here will be able to give you a lot of guidance.

Re: how to write a macro variable into existing dataset?

Ivan555 — Tue, 20 Aug 2019 09:17:29 GMT

@Patrick

SAS adds all tables of all libraries that you've got assigned in a SAS session to the SAS dictionary tables. These SAS dictionary tables are also accessible via views under library SASHELP.

Thank you, I see, that is very convenient.

Tables from libraries that point to databases will have a missing value for the row count

Unfortunately that is my case.

So I have to calculate some parameters myself..

If you clearly specify what you want to achieve then I'm sure people here will be able to give you a lot of guidance.

Speaking in terms of SASHELP.VTABLE I want to have filled with values columns
1. "nobs"
2. "filezise" (approximately, tables are very big)
for libraries that point to databases - that is my final goal.

But I dont know, will it be fair if I'll ask you to do whole task for me?

The subject in topic is narrower..

Re: how to write a macro variable into existing dataset?

PaigeMiller — Tue, 20 Aug 2019 10:44:34 GMT

other libraries I have dont have dictionaries.

Yes, they do have dictionary tables, it's the same table I mentioned SASHELP..VTABLE.

So there's no need for any macro code to do any of your steps.

Re: how to write a macro variable into existing dataset?

PaigeMiller — Tue, 20 Aug 2019 10:46:03 GMT

@Ivan555 wrote:

will it be fair if I'll ask you to do whole task for me?

No, in my opinion, that's not a fair question. You need to try to do it yourself, and if you get stuck, show us your code/log and we can help you from there.

Re: how to write a macro variable into existing dataset?

Patrick — Tue, 20 Aug 2019 11:33:27 GMT

@Ivan555

sashelp.vtable is a view that points to one of the actual SAS dictionary tables. You can either query the view or the dictionary table directly. You can also figure out where the actual dictionary table "lives" by looking into the view definition.

proc sql;
  describe view sashelp.vtable;
quit;

28         proc sql;
29           describe view sashelp.vtable;
NOTE: SQL view SASHELP.VTABLE is defined as:

        select *
          from DICTIONARY.TABLES;

30         quit;

To then get the full definition of this table you can execute

proc sql;
  describe table dictionary.tables;
quit;

SAS dictionary tables are READ ONLY. But you can of course create your own table by joining the dictionary table with additional information you're deriving yourself.

You're after a row count and the file size for database tables. This is information you need to retrieve by querying the database directly.

I don't know what filesize means in the context of a database so you have to work this out for yourself. Below sample code demonstrating how you could get the row counts. Row counts are in the data bases I work mostly with not a table attribute and for this reason not available as dictionary information. It requires a SQL select count(*) against every single table so that's going to be a costly process.

"will it be fair if I'll ask you to do whole task for me?"

Not really, no. That's what many of us are doing as a paid job so it's not for free. But well, I got inspired so below should already give you quite a lot.

Below code sample executes against SAS library WORK but it should work for any library pointing to a database. You will have to amend the code to fit your needs but it's likely already 80% of the solution for you.

options ls=max ps=max;

/* create some tables in library work */
data work.class work.class2;
  set sashelp.class;
  output work.class;
  if _n_<5 then output work.class2;
run;

/* create list of tables in Work */
proc sql;
  create table tableList as
  select l.engine, t.*
  from 
    (select distinct libname, engine from dictionary.libnames where libname='WORK') l 
    inner join 
    dictionary.tables t 
      on l.libname=t.libname and t.memtype='DATA' 
  order by libname, memname
  ;
quit;

/* execute select count(*) against all tables in WORK */
%macro countRows(outtbl, libname, memname, engine);
  %if %upcase(&memname) ne _THISCOUNT %then
    %do;
      %if &engine = V9 %then
        %do;
          /* do not execute a select count(*) against Base SAS tables */
          data _thisCount;
            length libname $8 memname $32 nlobs 8;
            stop;
          run;
        %end;
      %else
      %do;
        proc sql;
          create table _thisCount as
          select 
            "&libname" as libname length=8,
            "&memname" as memname length=32,
            count(*) as nlobs length=8
          from &libname..&memname
          ;
        quit;
      %end;
      proc append data=_thisCount base=&outtbl;
      run;
      proc datasets lib=work nolist nowarn;
        delete _thisCount;
        run;
      quit;
    %end;
%mend;

proc datasets lib=work nolist nowarn;
  delete _rowCounts;
  run;
quit;

data _null_;
  set tableList;
  call execute(cats('%countRows(_rowCounts,', libname, ',', memname, ',', engine, ')'));
run;

/* create new table by joining SAS dictionary table with result table from select count(*) */
data want;
  if _n_=1 then
    do;
      dcl hash h1(dataset:'_rowCounts');
      h1.defineKey('libname','memname');
      h1.defineData('nlobs');
      h1.defineDone();
    end;
  set tableList;
  if h1.find()=0 then nobs=nlobs;
run;

proc print data= want;
/*  var libname memname nobs nlobs;*/
run;

Please try to understand above code before asking for more.

Re: how to write a macro variable into existing dataset?

Ivan555 — Wed, 21 Aug 2019 08:13:13 GMT

@PaigeMiller

Yes, they do have dictionary tables, it's the same table I mentioned SASHELP..VTABLE.

Understood, I see them, thank you.

So there's no need for any macro code to do any of your steps.

Unfortunatelly I don't have columns "nobs" and "filesize" filled with values, so I think I have to calculate them manually.

No, in my opinion, that's not a fair question. You need to try to do it yourself, and if you get stuck, show us your code/log and we can help you from there.

I completely agree.

Next time I will try to make my head question shorter.

Concerning this topic - maybe one macro variable and one dataset could be enough to mark my problem (without sashelp tables and %macro)

@Patrick

I am very thankful for you for your concern.

I will very attentivly study it today and mabe tomorrow and give you reply.

Thank you.

Re: how to write a macro variable into existing dataset?

PaigeMiller — Wed, 21 Aug 2019 12:03:18 GMT

@Ivan555 wrote:

Concerning this topic - maybe one macro variable and one dataset could be enough to mark my problem (without sashelp tables and %macro)

No macros are needed. The data is there for your use in SASHELP.VTABLE

Re: how to write a macro variable into existing dataset?

Ivan555 — Wed, 21 Aug 2019 13:11:36 GMT

No macros are needed. The data is there for your use in SASHELP.VTABLE

Sorry, but it seems you don't listen to me..

This table is useful, thank you for the link, but it doesn't have the data, which I need.

Values in columns "nobs" and "filesize" are missing - emptiness inside.

The only library which has tables(rows in SASHELP.VTABLE), being filled with "obs" and "filesize" values is SASHELP

Re: how to write a macro variable into existing dataset?

Tom — Wed, 21 Aug 2019 13:20:33 GMT

You don't want to write a "macro variable" into the dataset. You want to store the record count into a variable in the dataset. In general you don't want to move data into macro variables (which are text) and then back into data. You can lose precision of the numbers and it just makes things more complicated.

Also before you try creating a macro program make sure you know what code you want to create.

data t_names;
  table_num +1;
  length table_name $256 table_cnt 8;
  input table_name;
datalines;
CLASS
CARS
;

proc sql;
update t_names
  set table_cnt = (select count(*) from sashelp.class)
  where table_name='CLASS'
;
quit;

proc print data=t_names;
run;

Once you know what code you want to generate you can then work on generating the code. It might be easier to generate this code from the original dataset instead of making a macro program.

filename code temp;
data _null_;
  set t_names;
  where missing(table_cnt);
  file code ;
  put 'update t_names set table_cnt = (select count(*) from sashelp.' table_name
       ')  where table_name=' table_name $quote. ';'
  ;
run;
proc sql;
 %include code / source2;
quit;

Re: how to write a macro variable into existing dataset?

PaigeMiller — Wed, 21 Aug 2019 14:02:20 GMT

@Ivan555 wrote:

No macros are needed. The data is there for your use in SASHELP.VTABLE

Sorry, but it seems you don't listen to me..

My apologies, it seems you are correct.

Fortunately, @Tom seems to have paid attention and found the answer.

Re: how to write a macro variable into existing dataset?

Ivan555 — Wed, 28 Aug 2019 09:52:13 GMT

I am back 🙂
I bring apologies to everyone, I was terribly busy last week.

I really appreciate any help, please do not try to see disrespect in my disappearance.

@Tom

Thank you, understood, that is exactly what I was trying to do.

The idea with writing code instead of macro is very smart, I haven't seen this ever before 🙂

Thank you.

@PaigeMiller

Thats is very fine that we understood each other. Thank you for your advices.

@Patrick

Thank you very much for your feedback.

In general, I understood your code and in the first approximation I don't have any questions about it.

I think on the next step I will try to combine your approach, Tom's approach and some my thoughts.

In my opinion, Tom's code is more consistent with the originally stated topic, it might be better to choose it as a solution.

Please tell me, if I create another topic like

Calculation of approximate Library size (which points to databases - tables have missing values of the sizes and rows counts)

, could you copy your post into it?
(this could be done to save important information - so that other members of the SAS Community could more easily find and maybe use it if there will be a need.)

Re: how to write a macro variable into existing dataset?

Patrick — Wed, 28 Aug 2019 10:07:19 GMT

@Ivan555

If you have a new question related to what has been discussed here then just start a new discussion and copy/paste a reference to the related discussion. Also mark the answer which helped you the most in the old discussion as solution so the discussion gets "closed".

As for what contributions you're in the end using: That's really fully up to you. I'm not taking any offence if that's not what I've posted.

Just as a comment to @Tom's code: If you have to care about performance then don't issue a select count(*) against SAS tables as there the row count is a table attribute (nlobs) which you can retrieve directly from the descriptor (metadata) part of the table. That's much faster than a select count(*) which processes the data.

Re: how to write a macro variable into existing dataset?

Tom — Wed, 28 Aug 2019 12:21:54 GMT

You can query metadata from some systems to get record counts, but that does not work for all. For example for VIEWs.

That is why the code I posted included this statement.

  where missing(table_cnt);

Re: how to write a macro variable into existing dataset?

Ivan555 — Fri, 30 Aug 2019 08:48:23 GMT

@Patrick

About:

If you have to care about performance then don't issue a select count(*) against SAS tables as there the row count is a table attribute (nlobs) which you can retrieve directly from the descriptor (metadata) part of the table.

I have missing values in all descritor parts..

Previosly you wrote:

Tables from libraries that point to databases will have a missing value for the row count because that's not a table attribute of database tables (like it is for SAS tables).

I think that is my case, so I have to calculate metadata for several libraries consisting of ~500 tables manually.. but this will be another topic I think.

I think during September I will write a code being based on your and Tom's solution.

I also think I will post it for information and will make a link on this discussion.

One more time: Big THX for Everyone, I am closing the topic.