Date:         Fri, 30 Jun 2006 09:11:04 -0700
Reply-To:     naveen.badugu@GMAIL.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         naveen.badugu@GMAIL.COM
Organization: http://groups.google.com
Subject:      Re: Export multiple datasets into a single worksheet.
Comments: To: sas-l@uga.edu
In-Reply-To:  <1151680983.304096.243130@p79g2000cwp.googlegroups.com>
Content-Type: text/plain; charset="iso-8859-1"

I do not wish to append all the datasets into 1 dataset. I want to
print each dataset one after the other in same excel worksheet ,
leaving space for 2-3 blank rows between each file. The point to note
here is that the structure of all these datasets keep varying on a week
on week basis, but all current week files have same structure.

I am using the following code to do so but it is only partially
correct.

/* Get the number of rows and columns in all target datasets */
proc contents data=target._all_ out=cont(keep=memname nobs);
run;
/* Get distinct rows and columns for each dataset */
proc sql;
 create table contents as
 select distinct memname, nobs as Rows, count(*) as Columns
 from cont group by memname, nobs;

select distinct memname into :memname separated by " " from cont;
Quit;
 /* Next I am using Koen Vyverman's sastoxl macro to automatically
print these datasets one after the other, but i dont seem to be getting
it right here */

%MACRO XYZ(list);
%LET num=1;
%LET wkbnm=%SCAN(&list,&num);
%PUT file &num = &wkbnm;
%DO %WHILE(&wkbnm NE );

%sastoxl(libin=target,
  dsin=&wkbnm,
  cell1row=&xxxx, /* I need to automatically get the value of nobs from
contents datasethere */
  cell1col=2,
  nrows=,
  ncols=,
  tmplpath=,
  tmplname=,
  sheet=June,
  savepath=&dir.\Target,
  savename=June,
  stdfmtng=);


data _null_;
slept= sleep(10);
run;

%LET num=%eval(&num+1);
%LET wkbnm=%scan(&list,&num);
%END;
%MEND XYZ;
%XYZ(&memname);

Hopefully someone can help me out here.
Thanks,
Naveen


naveen.badugu@gmail.com wrote:
> I have multiple SAS datasets which i get on a weekly basis and are of
> variable length
> Example:
> June1,June2....June7
> All these datasets have the same structure.
>
> I need to export all these datasets into a single excel worksheet tab.
> The required output is as follows:
>
> June1
> (3 blank lines)
> June2
> (3 blank lines)
> June3
> .
>
> How can i do this dynamically through a sas macro.