Help developing a macro with iterations

[SergioSanchez]

I have a table with macro variables created throught a proc sql

proc sql inobs=50;

select trim(testnumber), trim(table) into     :p1-:p50,

                                                            :table1-:tabla50              

from testimportcont;

quit;

and I'd like to iterate through the table ir order to store new macro variables

I'm trying

%macro counting;

proc sql;

%do i=1 %to 50;

select count (*) into :counting1-:counting50 from libtest.&tabla&i;

%end;

quit;

%mend;

but it doesn't work and I don't know how to continue....

One of my weakness are to work with macros, the other are iterate with variables

Thanks in advanced

[ballardw]

If you are going to try to use macros then you want to learn about the options MPRINT and SYMBOLGEN;

If you run:

options mprint;

before your call to %counting; you would see that the line

select count (*) into :counting1-:counting50 from libtest.&tabla&i;

overwriting your macro variables counting1 to counting50 with each iteration.

You might want to name the variables

:counting&i._1 - counting&i._50

so you have a different series of macro variables for each value of i

[Tom]

What are you actually trying to do?

Converting your data into macro variables and then back into data will probably cause trouble and probably is not necessary.

[Hima]

I used your code and adjusted it but I am not using into function to show you an example. Adjusting the below code slightly might acheive the results

you are looking for.

data have (drop=j);
do j=1 to 50;
  k=j;
  output;
end;
;
run;

%let i=50;

%macro counting;
%IF &i GT 0 %THEN
  %DO;
   %do j=1 %to 50;

    proc sql;
     select count(*) as counting&j. from have;
    quit;

   %end;
  %end;
%mend;

%counting

Output is attached for the first 10 and last10

I hope it would be good start

[SergioSanchez]

Hi all

Thanks Hima for your effort but  my macro's level is not enought for your code :smileycry::smileycry:, but it's much apreciated for me.

ballardw, that is not he problem as you can see in the code below

Tom, I have to run several test that count observations in various dataset so First I import the results from past days to a dataset, the spreedsheet is like this:

test table descript  count   date

===     =====   ========  =====   ====

test1 table1 zzzzzzzz 25 10/09/2014

.... .... .... .. .....

What I'm trying to do is take test1 to test 50, table1 to table50, descript1 to descript50 and convert into macro variables throught the proc sql statement select into.

After this I'm thinking about get the count value for each test and table and store it in others macrovariables.

Finally, with the values of count1-count50 perform a proc sql insert into, into the dataset I imported from excel to acumulate the results of the days and export again to excel file.

So I have managed the first step with de proc sql I posted in the post above

The second step is to count the observations and here is where I locked because when I try to run

76   %countobs

MLOGIC(COUNTOBS):  Beginning execution.

MPRINT(COUNTOBS):   proc sql;

MLOGIC(COUNTOBS):  %DO loop beginning; index variable I; start value is 1; stop value is 29;

      by value is 1.

NOTE: Line generated by the invoked macro "COUNTOBS".

3     select count (*) into :counting&i from dmpro.&table.i;

                                                   -

                                                   22

                                                   76

WARNING: Apparent symbolic reference TABLA not resolved.

MPRINT(COUNTOBS):   select count (*) into :counting1 from dmpro.&;

ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

MLOGIC(COUNTOBS):  %DO loop index variable I is now 2; loop will iterate again.

NOTE: Line generated by the invoked macro "COUNTOBS".

5     select count (*) into :counting&i from dmpro.&table.i;

                                                   -

                                                   22

                                                   76

WARNING: Apparent symbolic reference TABLA not resolved.

MPRINT(COUNTOBS):   select count (*) into :counting2 from dmpro.&;

ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

But if I write %put _user_ I can see all the tables (from table1 to table50) with its values

Regards

[Tom]

I am not sure I understand what you want.  Perhaps you are saying that you have a list of dataset names and you want to find the number of observations for each of those datasets?

data have;

  input test $ table $ ;

cards;

test1 have

test2 table2

;;;

proc sql noprint ;

  create table want as

    select a.*

         , b.nobs

    from have a

    left join dictionary.tables b

    on b.libname='WORK' and upcase(a.table) = b.memname

  ;

quit;

proc print;

run;

[SergioSanchez]

Yes, that´s right.

Very good answer, clean and easy, but unfortunately it´s doens't work for me because the datasets are not in my local drive, but if in a remote server so I don't see how many observations are in these datasets

As I said to Jaap in my sashelp.vmacro table I see all the macros I created with the select into: but when I try to access to them but without success

Edit: I got it. There was a sintax issue, now the code iterates but without stores de results of the query in the macro variables counting1,counting2  and so on

%macro countobs(table) ;

proc sql noprint;

%do i=1 %to 29;

select count (*) into :counting&i from dmpro.&&table&i;

%end;

quit;

%mend;

MPRINT(COUNTOBS):   select count (*) into :counting25 from libname.table25;

[Tom]

You still do not need to resort to macro "arrays" or store the counts into macro variables.

Note that if you are storing the count into a macro variable make sure to use a format or it will lose precision for counts that require more than 12 digits.  (select count(*) format=18.)

For example you could just store the list of tables into a single macro variable and loop over that and generate UPDATE statements to set the NOBS variable with the COUNT(*) results.

data have;

  input test $ libname $ memname $ ;

cards;

test1 work have

test2 sashelp class

;;;

data want;

set have;

nobs=.;

run;

%macro test;

%local tables i ;

*----------------------------------------------------------------------;

* Count number of observations when NOBS is missing ;

*----------------------------------------------------------------------;

  proc sql noprint;

    select distinct catx('.',libname,memname)

      into :tables separated by '/'

    from WANT

    where nobs is missing

    ;

  %do i=1 %to &sqlobs;

    update WANT set nobs =

      (select count(*) from %scan(&tables,&i,/))

      where catx('.',libname,memname)="%scan(&tables,&i,/)"

    ;

  %end;

  quit;

%mend test;

%test;

[SergioSanchez]

Morning all

Thanks Tom, it works fine.

Only a few questions for us .

Sorry Jaap but I don't know what is RLS. Could you light to me please?

Tom, I've never seen before the statement "%local tables i ;". Would you explain to me how to use it?

Thanks

[jakarman]

When you have SAS running on several servers you can connect those using SAS/connect.
By doing that you can remote submit SAS-programs between the sas sessions, doing up/down loads and using remote library servers (RLS).

RLS is making the library transparent in a functional way looking at it on the local machine while it on server side.

When you are using SAS/Access to a RDBMS you are also connecting to External data. But in that case is not a SAS session running at the other side.   

[SergioSanchez]

thank

Jaap Karman escribió:

When you have SAS running on several servers you can connect those using SAS/connect.
By doing that you can remote submit SAS-programs between the sas sessions, doing up/down loads and using remote library servers (RLS).
RLS is making the library transparent in a functional way looking at it on the local machine while it on server side.

When you are using SAS/Access to a RDBMS you are also connecting to External data. But in that case is not a SAS session running at the other side.    

Thank you so much Jaap

[Tom]

%LOCAL will insure that the macro variables are defined in the current macro's symbol table.  So changes made to them will not impact any existing macro variables that were created before the macro started running.

[SergioSanchez]

Tom escribió:

%LOCAL will insure that the macro variables are defined in the current macro's symbol table.  So changes made to them will not impact any existing macro variables that were created before the macro started running.

Thanks Tom for share your knowledge with me and thanks damanaulakh88 for your code, it was my first approach to the problem but for some reason I still don't resolved why the counting1-:counting4 are not store like macro variables, in fact they don't appear when you submit %put _all_ statement.

Do you know what could be the reason fo it?

Regards

[jakarman]

All information on the tables are available using either:

- proc contents

- SAShelp.v-       entries  There are many of them. Macro-values libnames and table
                            The table entrie wil give you all information on all tables that are open in your sas session. including nobs.

- SAS macro's    are able to get it on a single-dataset approach. very handy when to nobs need to be known in advance.

[SergioSanchez]

Hi Jaap

If I go to sashelp.Vmacro I can see all the macro variables I created with the select into.

The problem is that I don't know how to call that macros (table1,table2, and so on)