Exporting datasets takes long time

[nandanosho]

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.

[Scott_Mitchell]

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

[nandanosho]

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 #

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;

[RW9]

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?