Prev: Problem when using the SAS/GRAPH Procedures: SGPLOT and SGPANEL
Next: Notes produced from proc mixed
From: Tom Abernathy on 22 Jan 2010 13:28
A couple of SQL tips for macro variables.
1) You do not need to count the number records in advance, you can get
the count from SQLOBS. Also if you tell it to create say 1000
variables and there are only 10 values it will only create the
variables it needs. So your SQL code could be:
proc sql noprint;
select table_name into :var1-var1000 from all_table_names;
quit;
%let cnt=&sqlobs;

2) You could generate your statements into a single macro variable.
And then expand the macro variable to run. That way you do not need to
wrap it inside a macro.

proc sql noprint;
select 'proc append force base=base_table data='||table_name
into :code separated by ';run;'
from all_table_names;
quit;
&code



On Jan 21, 11:06 am, shiva <shiva.said...(a)gmail.com> wrote:
> Hi Wendy,
>
> Sorry for untested code.
>
> Try this ...hope this helps..
>
> %macro test;
> proc sql;
>     select count(table_names) into :cnt from all_table_names;
>     select table_names into :var1 - :var%TRIM(%LEFT(&cnt)) from
> all_table_names;
> quit;
> %do i=1 %to &cnt;
>  %let table=&&var&i;
> proc append base=base_table data=&&table force;
> run;
> %end;
> %mend test;
> %test;
>
> Thanks;
> shiva

From: =?ISO-8859-1?Q?Daniel_Fern=E1ndez?= on 22 Jan 2010 13:40
hi,

this will work for you:


%macro appendn ;
proc sql;
select count(table_names) , table_names
into :cnt , :tables_list separated by '/' from
from all_table_names;
quit;

%do i=1 %to &cnt;
%let table=%sysfunc(scan(&tables_list,&i,'/'));
proc append base=base_table data=&table force;
run;
%end;
%mend;

%appendn;


I recommend you to write this before calling your macro
to help you to debugging:

options mprint symbolgen;

Type 'nomprint nosymbolgen' to get them off.


Daniel Fernandez.
Barcelona.

2010/1/22 shiva <shiva.saidala(a)gmail.com>:
> Hi,
>
> I think there is empty space in macro variable cnt.
>
> Try to store that and resolve it.
>
> %macro test;
> %let cnt1=&&cnt;
> %do i=1 %to &cnt1;
> %let table=&&var&i;
> proc append base=base_table data=&&table force;
> run;
> %end;
> %mend test;
> %test;
>
> Thanks,
> shiva
>
First  |  Prev  | 
Pages: 1 2 3
Prev: Problem when using the SAS/GRAPH Procedures: SGPLOT and SGPANEL
Next: Notes produced from proc mixed