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.
Can you provide the code you are using and a sample of the dataset, so we can see what you are doing?
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
,¶meter as %substr(¶meter.,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;
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.