topic Re: Exporting datasets takes long time in SAS Enterprise Guide

Exporting datasets takes long time

nandanosho — Fri, 04 Jul 2014 06:37:19 GMT

Hello All,

I want to export a datasets which having more than 20 k records.And also need to customized the reports so I am using the tagsets.excelxp option to export.

But it taking a lot of time and also file size becomes too large.

So Please suggest.

Thanks and Regards.

Re: Exporting datasets takes long time

Scott_Mitchell — Fri, 04 Jul 2014 08:42:34 GMT

Can you provide the code you are using and a sample of the dataset, so we can see what you are doing?

Re: Exporting datasets takes long time

RW9 — Fri, 04 Jul 2014 08:51:01 GMT

To note, the excelxp tagset creates XML output, which by its nature is very verbose. I would firstly ask why you would want 20k records in Excel, which is not a database tool, as its impractical to actually do anything with the data in Excel. If you want reports, then generate the output from SAS.

If you really must output large quatities to Excel, then using CSV delimited output would result in probably the smallest file size. Perhaps an explanation of what you are trying to achieve?

Re: Exporting datasets takes long time

nandanosho — Fri, 04 Jul 2014 08:51:40 GMT

Thanks for response.

Please find the code...

proc sql;

create table &lib1..Common as

select a.WEATHER_STATION_CODE

,a.LEVEL2_CODE

,a.LEVEL3_NAME

,a.LEVEL4_NAME

,a.LEVEL5_NAME

,a.LEVEL6_NAME

,a.LEVEL7_NAME

,a.LEVEL8_NAME

,a.WEATHER_STATION_NAME

from WEATHER_STATION a

where a.WEATHER_STATION_CODE in (select b.WTD_WS_CODE from WEATHER_DATA b)

order by WEATHER_STATION_CODE;

quit;

data WHEATHER_STATION_COMMON;

set common;

N=_N_;

run;

proc transpose data=WHEATHER_STATION_COMMON

out=tara(drop=_label_ rename=(_name_=PARA));

VAR LEVEL2_CODE WEATHER_STATION_CODE WEATHER_STATION_NAME

LEVEL3_NAME LEVEL4_NAME LEVEL5_NAME LEVEL6_NAME LEVEL7_NAME

LEVEL8_NAME;

run;

proc sql feedback;

select max(n)into : num

from WHEATHER_STATION_COMMON;

quit;

options mlogic mprint symbolgen;

%macro weather_data;

%do i=1 %to &num;

proc sql;

select WEATHER_STATION_CODE into: Station&i.

from WHEATHER_STATION_COMMON

where n=&i.;

quit;

proc sql;

create table &lib1..wheather_data_&&station&i. as

select WTD_WEATHER_DATE

,&parameter as %substr(&parameter.,9,5)_&&station&i.

from WEATHER_DATA

where WTD_WS_CODE="%trim(&&station&i)"

order by WTD_WEATHER_DATE;

quit;

%end;

data all;

merge %do i=1 %to %eval(&num); wheather_data_&&station&i. %end;;

by WTD_WEATHER_DATE;

run;

%mend weather_data;

%weather_data;

ods tagsets.excelxp file="&path\Data report.xls" style=minimal

options(AUTOFIT_HEIGHT='yes');

ods tagsets.excelxp options(sheet_interval='none' sheet_name="DATA_REPORT");

proc print data=TARA;

run;

proc print data=All;

run;

ods tagsets.excelxp close;

proc datasets library=work;

delete

All

wh:

;

run;