topic Proc Export SAS database has more rows than Excel in New SAS User

Proc Export SAS database has more rows than Excel

texasmfp — Thu, 09 Jan 2020 19:44:21 GMT

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?

Re: Proc Export SAS database has more rows than Excel

Kurt_Bremser — Thu, 09 Jan 2020 20:08:07 GMT

Use the obs= dataset option.

Re: Proc Export SAS database has more rows than Excel

JeffMaggio — Thu, 09 Jan 2020 21:09:44 GMT

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;

Re: Proc Export SAS database has more rows than Excel

texasmfp — Thu, 09 Jan 2020 21:48:25 GMT

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

Re: Proc Export SAS database has more rows than Excel

Kurt_Bremser — Thu, 09 Jan 2020 21:57:15 GMT

Use the dataset option on the incoming dataset.

Re: Proc Export SAS database has more rows than Excel

Tom — Thu, 09 Jan 2020 21:57:59 GMT

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

Re: Proc Export SAS database has more rows than Excel

texasmfp — Fri, 10 Jan 2020 01:36:41 GMT

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;