BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
texasmfp
Lapis Lazuli | Level 10

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?

1 ACCEPTED SOLUTION

Accepted Solutions
texasmfp
Lapis Lazuli | Level 10

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;

View solution in original post

6 REPLIES 6
JeffMaggio
Obsidian | Level 7
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;
texasmfp
Lapis Lazuli | Level 10

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

Tom
Super User Tom
Super User

@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.

texasmfp
Lapis Lazuli | Level 10

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 6 replies
  • 2940 views
  • 2 likes
  • 4 in conversation