DATA Step, Macro, Functions and more

PROC EXPORT : Export data to three(multiply) different sheets in Excel

Accepted Solution Solved
Reply
Super Contributor
Posts: 381
Accepted Solution

PROC EXPORT : Export data to three(multiply) different sheets in Excel

I want to export the command below to excel as 3 different sheet or 3 different excel files. I want it to do this every 900.000 rows. And ı have tried another method and it didnt work. Can you help me about this? Unfortunately i dont have data about the problem. So i can only send the code.

 

PROC EXPORT DATA= HVERGI.Sorgulanacak_vergi_plaka_1 (WHERE=( _N_ < 900001))
            OUTFILE= "D:\SAS\HVERGI\SORGULANACAK_VERGI_PLAKA_1_2.xlsx" 
            DBMS=EXCEL REPLACE;
     SHEET="VP1"; 
RUN;
/*---------------------------------------------------------------------------------------------------------------*/
options obs= 900000;
PROC EXPORT DATA= HVERGI.Sorgulanacak_vergi_plaka_1 DBMS=xlsx OUTFILE= "D:\SAS\HVERGI\SORGULANACAK_VERGI_PLAKA_1_3.xlsx" REPLACE; RUN; options firstobs= 900001; options obs= 1800000; PROC EXPORT DATA= HVERGI.Sorgulanacak_vergi_plaka_1 DBMS=xlsx OUTFILE= "D:\SAS\HVERGI\SORGULANACAK_VERGI_PLAKA_1_4.xlsx" REPLACE; RUN; options firstobs= 1800001; options obs= max; PROC EXPORT DATA= HVERGI.Sorgulanacak_vergi_plaka_1 DBMS=xlsx OUTFILE= "D:\SAS\HVERGI\SORGULANACAK_VERGI_PLAKA_1_5.xlsx" REPLACE; RUN;

Accepted Solutions
Solution
‎10-14-2015 04:50 PM
Super User
Super User
Posts: 6,502

Re: PROC EXPORT : Export data to three(multiply) different sheets in Excel

Use the dataset options FIRSTOBS and OBS rather than the system options.

No need to post your data as we can write the program using one of the available datasets in SASHELP library.

 

%let path=%sysfunc(pathname(work));

proc export data=sashelp.class (firstobs=1 obs=6)
   outfile= "&path/one.xlsx"
   dbms=xlsx replace
;
  sheet="one";
run;

proc export data=sashelp.class (firstobs=7 obs=12)
   outfile= "&path/two.xlsx"
   dbms=xlsx replace
;
  sheet="two";
run;

View solution in original post


All Replies
Super User
Posts: 10,516

Re: PROC EXPORT : Export data to three(multiply) different sheets in Excel

I expect that you had a number of error messages. It is often helpful to explain those when shown.

 

When using the Firstobs and obs DATASET options they go in parantheses after the data set:

 

data = mydata (firstobs=900001 obs=1800000);

Super Contributor
Posts: 381

Re: PROC EXPORT : Export data to three(multiply) different sheets in Excel

Thank you ballardw.

I ran the second code i created and I didnt receive any errors. Though, is there another method? Such as writing as sheets and writing seperate excel files. For example, first 900000 rows will be one sheet and second 900000 rows will be another sheet. 

Super Contributor
Posts: 381

Re: PROC EXPORT : Export data to three(multiply) different sheets in Excel

In the first code, program see _n_ as like column and gets error. after the where statement what do i have to write instead of _n_. ? As you know _n_ option provides rows of count.

 

Thank you.

Super Contributor
Posts: 381

Re: PROC EXPORT : Export data to three(multiply) different sheets in Excel

Is it possible to use where statement to reach my aim. As i said i tried the code like as below but it didn't work.

 

PROC EXPORT DATA= HVERGI.Sorgulanacak_vergi_plaka_1 (WHERE=( _N_ < 900001))
            OUTFILE= "D:\SAS\HVERGI\SORGULANACAK_VERGI_PLAKA_1_2.xlsx" 
            DBMS=EXCEL REPLACE;
     SHEET="VP1"; 
RUN;
Solution
‎10-14-2015 04:50 PM
Super User
Super User
Posts: 6,502

Re: PROC EXPORT : Export data to three(multiply) different sheets in Excel

Use the dataset options FIRSTOBS and OBS rather than the system options.

No need to post your data as we can write the program using one of the available datasets in SASHELP library.

 

%let path=%sysfunc(pathname(work));

proc export data=sashelp.class (firstobs=1 obs=6)
   outfile= "&path/one.xlsx"
   dbms=xlsx replace
;
  sheet="one";
run;

proc export data=sashelp.class (firstobs=7 obs=12)
   outfile= "&path/two.xlsx"
   dbms=xlsx replace
;
  sheet="two";
run;
Super Contributor
Posts: 381

Re: PROC EXPORT : Export data to three(multiply) different sheets in Excel

Thank you Tom. I will consider your recommendation but it goes without saying that sometimes i need to post my data to better explanation.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 232 views
  • 0 likes
  • 3 in conversation