I have a large database (more than 10M obs) and I want to export to Excel which caps the obs at just over 1M. I want to take the top 1M obs (using the value of one variable) and export just those 1M into Excel. How can I limit the obs exported to Excel?
This worked. Thanks to all.
data inlib.&top_set;
set top_set (obs=10000);
run;
proc export
data=inlib.&top_set
outfile="&ROOT\9 Primary Targets (2018 Data) Summary.xlsx"
dbms=xlsx replace;
SHEET="Raw";
run;
proc sort data=have out=top_1M (obs=1000000);
by descending one_variable;
run;
proc export
data=top_1M
dbms=xlsx
outfile="C:\somepath\somefilename.xlsx"
replace;
run;
Thanks Jeff: I thought that would work, but it failed to limit the obs:
42 proc sort data= inlib.&full_set nodup out=top_set (obs=1000000);
43 by descending sum;
44 run;
NOTE: There were 9882141 observations read from the data set INLIB.FULL_SET.
NOTE: 190767 duplicate observations were deleted.
NOTE: The data set WORK.TOP_SET has 9691374 observations and 11 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 47.80 seconds
cpu time 15.46 seconds
Use the dataset option on the incoming dataset.
@texasmfp wrote:
Thanks Jeff: I thought that would work, but it failed to limit the obs:
42 proc sort data= inlib.&full_set nodup out=top_set (obs=1000000); 43 by descending sum; 44 run; NOTE: There were 9882141 observations read from the data set INLIB.FULL_SET. NOTE: 190767 duplicate observations were deleted. NOTE: The data set WORK.TOP_SET has 9691374 observations and 11 variables. NOTE: PROCEDURE SORT used (Total process time): real time 47.80 seconds cpu time 15.46 seconds
You need to use OBS= on an INPUT dataset. You are trying to use it on an OUTPUT dataset.
This worked. Thanks to all.
data inlib.&top_set;
set top_set (obs=10000);
run;
proc export
data=inlib.&top_set
outfile="&ROOT\9 Primary Targets (2018 Data) Summary.xlsx"
dbms=xlsx replace;
SHEET="Raw";
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.