topic Re: Help developing a macro with iterations in SAS Programming

Help developing a macro with iterations

SergioSanchez — Tue, 09 Sep 2014 16:18:34 GMT

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

Re: Help developing a macro with iterations

ballardw — Tue, 09 Sep 2014 20:20:11 GMT

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

Re: Help developing a macro with iterations

Tom — Tue, 09 Sep 2014 20:37:22 GMT

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.

Re: Help developing a macro with iterations

Hima — Tue, 09 Sep 2014 20:49:24 GMT

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

Re: Help developing a macro with iterations

SergioSanchez — Wed, 10 Sep 2014 07:45:28 GMT

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;

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;

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

Re: Help developing a macro with iterations

jakarman — Wed, 10 Sep 2014 09:06:20 GMT

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.

Re: Help developing a macro with iterations

SergioSanchez — Wed, 10 Sep 2014 10:23:42 GMT

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)

Re: Help developing a macro with iterations

jakarman — Wed, 10 Sep 2014 10:35:30 GMT

if you go to sashelp.vtable you will see all the tables and their number of observations
It is a table you can process that as a table.

Re: Help developing a macro with iterations

Tom — Wed, 10 Sep 2014 12:02:18 GMT

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;

Re: Help developing a macro with iterations

SergioSanchez — Wed, 10 Sep 2014 14:09:26 GMT

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;

Re: Help developing a macro with iterations

jakarman — Wed, 10 Sep 2014 14:56:17 GMT

You can submit that code of Tom to your remote session.
Having a RLS working it will run. Is it an external RDBMS you need to have the count(*) approach.

Some RDBMS are supporting the retrieval of metadata information.

Re: Help developing a macro with iterations

Tom — Wed, 10 Sep 2014 15:12:25 GMT

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;

Re: Help developing a macro with iterations

SergioSanchez — Thu, 11 Sep 2014 07:25:16 GMT

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

Re: Help developing a macro with iterations

jakarman — Thu, 11 Sep 2014 07:36:51 GMT

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.

Re: Help developing a macro with iterations

SergioSanchez — Thu, 11 Sep 2014 09:10:19 GMT

thank

Jaap Karman escribió:

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

Re: Help developing a macro with iterations

damanaulakh88 — Thu, 11 Sep 2014 09:36:40 GMT

Here is the code you can use:

/*Creating Datasets*/

data a;
input name$ emp;
datalines;
aaa 121
bb 232
ccc 989
;
run;

data b;
input name$ emp;
datalines;
aaa 121
ccc 989
;
run;
data c;
input name$ emp;
datalines;
aaa 121
bb 232
;
run;
data d;
input name$ emp;
datalines;
aaa 121
bb 232
ccc 989
erf 455
;
run;

data testimportcont;
input testnumber $ table $;
datalines;
a1 a
a2 b
a3 c
a4 d
;
run;
proc sql inobs=50;
select trim(testnumber), trim(table) into :p1-:p4,:tab1-:tab4
from testimportcont;
quit;

options mprint mlogic symbolgen;
%macro counting;
proc sql;
%do i=1 %to 4;
select count(*) into :counting1-:counting4 from &&&tab&i.;
%end;
quit;
%mend;
%counting;

=====Output=====

Hope this helps.

Re: Help developing a macro with iterations

Tom — Thu, 11 Sep 2014 11:09:41 GMT

%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.

Re: Help developing a macro with iterations

SergioSanchez — Fri, 12 Sep 2014 09:10:58 GMT

Tom escribió:

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