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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.