Date: Fri, 7 Jul 2006 10:18:14 -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.
In-Reply-To: <200607032313.k63AkUFW008483@mailgw.cc.uga.edu>
Content-Type: text/plain; charset="iso-8859-1"
Hi Howard,
Thanks for your solution, it was very helpful. I wish to know how is it
possible to write to the same excel file "June.xls" again and again
into different tabs(June, July, Aug...etc) using the same sastoxl
macro. I think your code will work all the same but will I have to
clear the existing filename calls.
Supposing in the below code sent by you I wish to add the following:
where = (libname='TARGET' and memtype='DATA' and memname contains
'JUNE')
where = (libname='TARGET' and memtype='DATA' and memname contains
'JULY')
/* I want to display 2 or more tabs like June and July with appended
data just as your code and save the file as June. I have tried
repeating the data _null_ again but it does not overwrite existing file
June */
Thanks,
Naveen
data _null_;
> set sashelp.vtable
> (keep = libname memtype memname nobs
> where = (libname='TARGET' and memtype='DATA') );
> length sastoxlcall $ 200;
> retain xlrow 1;
> sastoxlcall = '%sastoxl'
> || '(libin=target'
> || ',dsin=' || trim(memname)
> || ',cell1row=' || compress(put(xlrow,8.) )
> || ',cell1col=2'
> || ',sheet=June'
> || ',savepath=c:\temp\Target'
> || ',savename=June'
> ;
> if _n_=1 then sastoxlcall = trim(sastoxlcall)
> || ')'
> ;
> else sastoxlcall = trim(sastoxlcall)
> || ',tmplpath=c:\temp\Target'
> || ',tmplname=June'
> || ')'
> ;
> file calls;
> put sastoxlcall;
> *call execute(sastoxlcall);
> xlrow + nobs + 3 + 1;
> run;
"Howard Schreier <hs AT dc-sug DOT org>" wrote:
> On Fri, 30 Jun 2006 09:11:04 -0700, naveen.badugu@GMAIL.COM wrote:
>
> >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.
>
> It turns out that that does not matter. The solution will automatically
> handle differences.in 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
>
> That uses Dynamic Data Exchange (DDE). Lots of people scorn DDE these days.
> It's true that the newer tools can do some nice things, and aren't as fussy
> as DDE, but when it comes to fine control (as called for here), DDE can do
> things which other techniques cannot.
>
> >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
>
> It can be simplified; no outer macro, and just one DATA step to tap the
> metadata and generate the SASTOXL macro (which, by the way, is available at
> http://www.sas-consultant.com/professional/sastoxl-for-SUGI26.sas).
>
> Create the library:
>
> libname target 'c:\temp\target';
>
> Dump some data sets into it, for testing:
>
> proc datasets library=sashelp;
> copy out=target;
> select class retail shoes;
> run;
> quit;
>
> Create a temporary catalog entry to hold the macro calls:
>
> filename calls catalog 'work.sastoxlcalls.source' lrecl=200;
>
> Generate the macro calls:
>
> data _null_;
> set sashelp.vtable
> (keep = libname memtype memname nobs
> where = (libname='TARGET' and memtype='DATA') );
> length sastoxlcall $ 200;
> retain xlrow 1;
> sastoxlcall = '%sastoxl'
> || '(libin=target'
> || ',dsin=' || trim(memname)
> || ',cell1row=' || compress(put(xlrow,8.) )
> || ',cell1col=2'
> || ',sheet=June'
> || ',savepath=c:\temp\Target'
> || ',savename=June'
> ;
> if _n_=1 then sastoxlcall = trim(sastoxlcall)
> || ')'
> ;
> else sastoxlcall = trim(sastoxlcall)
> || ',tmplpath=c:\temp\Target'
> || ',tmplname=June'
> || ')'
> ;
> file calls;
> put sastoxlcall;
> *call execute(sastoxlcall);
> xlrow + nobs + 3 + 1;
> run;
>
> A new Excel workbook will be created for the first data set. Subsequent
> calls will open it and append data per the requirements statement. That's
> the purpose of the two additional parameters.
>
> Finally launch:
>
> %include calls;
>
> That should create the Excel worksheet with all three tables.
>
> The following just dumps the macro calls to the log:
>
> data _null_;
> infile calls;
> input;
> put _infile_;
> run;
>
> They look like this:
>
> %sastoxl(libin=target,dsin=CLASS,cell1row=1,cell1col=2,
> sheet=June,savepath=c:\temp\Target,savename=June)
>
> %sastoxl(libin=target,dsin=RETAIL,cell1row=24,cell1col=2,
> sheet=June,savepath=c:\temp\Target,savename=June,
> tmplpath=c:\temp\Target,tmplname=June)
>
> %sastoxl(libin=target,dsin=SHOES,cell1row=86,cell1col=2,
> sheet=June,savepath=c:\temp\Target,savename=June,
> tmplpath=c:\temp\Target,tmplname=June)
>
> My question: When I omit the %INCLUDE and instead un-comment the CALL
> EXECUTE, it all fails. I get
>
> ERROR: A character operand was found in the %EVAL function
> or %IF condition where a numeric operand is required.
> The condition was: 200*
>
> ERROR: The macro SASTOXL will stop executing.
>
> and that repeats, once for each data set. But I am submitting the esact same
> series of macro calls, whether via CALL EXECUTE or %INCLUDE. What is the
> problem?
>
> >
> >
> >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.
| 
