PROC EXPORT DATA to OUTFILE Giving Error

[elwayfan446]

Hello,

While researching a way to export records from a dataset to excel workbooks based on Sequence number I stumbled across this old topic:

https://communities.sas.com/t5/SAS-Procedures/Problem-with-PROC-EXPORT/td-p/88349

I am having trouble with the solution because I am getting the error "Server Name is invalid or missing."

Here is the code I am running:

proc sort data=sellersheet out=loop (keep=SeqNo seller_name)nodupkey;
by SeqNo;
run;

data _null_;
  set loop;
  call execute(cats('proc export data=sellersheet (where=(SeqNo="',SeqNo,'")) outfile="/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/&filedate.',seller_name,'.xlsx";sheet="','LOANLIST','"; run;'));
run;

The error I am receiving during the call execute step:

The file path is on a server and not on a network or PC.  I can't figure out if it has something to do with that or not.

[Kurt_Bremser]

You missed to add the DBMS=XLSX option to the PROC EXPORT statement.

[elwayfan446]

@Kurt_Bremser thank you for your reply.   I had tried that yesterday and was getting an error.  I had moved on from that but it was exactly what was wrong.  I believe I had it in the wrong spot of the syntax.  Thank you!

Following up... is there a way to exclude the variables in my proc export that I am using to query the records?

I would like to drop SeqNo and seller_name from the exported file but since I am using them for the query, it throws an error if I try to use the DROP= statement

(where=(SeqNo=',SeqNo,'))

outfile="/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/&filedate._',seller_name,'.xlsx"

Thanks!

[Kurt_Bremser]

Such minuscule log snippets do not help in locating the problem. Please post complete code, and complete logs for steps that fail.

[Tom]

Make a copy of that data and export that.  Instead of just generating the PROC EXPORT code

proc export data=ORIGINAL(where=(seqno='xxx')) ....

generate code like this instead:

data for_export ;
  set ORIGINAL;
  where seqno='xxx';
  drop seqno;
run;
proc export data=for_export ....

[elwayfan446]

Thanks @Tom .

I think I still have the same problem even if I do that.  Here is all of the code I am running:

I am looping through each seller to get the SeqNo that was assigned to each in the dataset sellersheet:

proc sort data=sellersheet out=loop (keep=SeqNo seller_name)nodupkey;
by SeqNo;
run;

In my CALL EXECUTE() I have to export a new excel file for every SeqNo so I am comparing the SeqNo in the loop dataset to the SeqNo in the sellersheet dataset so all of the info from the sellersheet dataset gets exported into the file for its SeqNo.

data _null_;
  set loop;
  call execute(cats('proc export data=sellersheet (where=(SeqNo=',SeqNo,')) outfile="/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/&filedate._',seller_name,'.xlsx" DBMS=XLSX REPLACE;sheet="','LOANLIST','"; run;'));
run;

I don't know how I can pull from any dataset to export into individual files if I drop the SeqNo.  I must be misunderstanding.

[Tom]

You need to change the code you are generating.

So instead of starting like:

 call execute(cats('proc export 

It needs to start with generating the data step to subset the data and drop the variable.

 call execute(cats('data 

I posted example code you need to generate. You just need to translate that into the code needed to generate that code and pass it to CALL EXECUTE.  

[elwayfan446]

I am still not following on how generating this code will translate into the export of multiple excel files along with being able to use the variables I don't want included in the file name of the excel files.

If I generate the code you posted above:

data for_export ;
  set ORIGINAL;
  where seqno='xxx';
  drop seqno;
run;
proc export data=for_export ....

In the where clause, what am I comparing seqno to?  The ORIGINAL dataset?  I need all the records from it so I am not trying to filter anything out. 

I am sorry... I am just not understanding.  I am trying for 2 objectives here and that might be what is muddying the water.

1. I need to create a separate excel file from the sellersheet dataset that includes all records with the same sequence number (one file per sequence number/lender)
2. I need the file name of the excel files to include seller_name of each record in that dataset, however, I don't want seqno or seller_name included in the actual data.

Originally, with this code I achieved getting the seller name in the file.  It creates a dataset called loop so that the EXECUTE() statement can loop through and create a new file every time it sees a new seqno.  That is why I have to include it in the where clause of the EXECUTE(). 

proc sort data=sellersheet out=loop (keep=SeqNo seller_name)nodupkey;
by SeqNo;
run;

data _null_;
  set loop;
  call execute(cats('proc export data=sellersheet (where=(SeqNo=',SeqNo,')) outfile="/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/&filedate._',seller_name,'.xlsx" DBMS=XLSX REPLACE;sheet="','LOANLIST','"; run;'));
run;

[Tom]

I am not sure what is confusing.  Currently you are using a data step to generate multiple PROC EXPORT steps.  Each time through the data step you are generating code like this:

proc export data=sellersheet (where=(SeqNo='xxx')) 
  outfile="/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/YYYYYY_ZZZZZZ.xlsx" 
  DBMS=XLSX REPLACE
;
  sheet="LOANLIST"; 
run;

Instead your data step needs to generate first a data step and then the proc export step.

Personally I prefer to use the PUT statement to write the generated code to a file instead of using CALL EXECUTE.  Then you can review the generated code and make sure the syntax is correct.  Once you are sure the data step is generating valid code you just add a %INCLUDE statement to run the generated statements.

[elwayfan446]

The confusing part is how I get what I need when I perform the data step for the export file.  In your where clause example, you are using a constant

(where=(SeqNo='xxx')) 

To export the data to each spreadsheet based on the SeqNo from the original dataset (sellersheet) I have to pass the SeqNo to the proc export statement.  If I am creating a new dataset to use for the export and dropping the SeqNo so it doesn't show up in the data in each of the files, how will that work any differently?

[elwayfan446]

Good morning @Tom 

Here is my latest crack at it but doing this now puts all records from the dataset into each excel spreadsheet.  I can't make the connection to only export the records for each seqno (seller_name) into their own spreadsheet.

data _null_;
  set loop;
  call execute(cats(
  				'proc sort data=sellersheet out=loop (keep=SeqNo seller_name)nodupkey;
				 by SeqNo;
				 run;

  				 data for_export;
				   set sellersheet;
				   where seqno=SeqNo;
				   drop seqno;
				 run;

				 proc export data=for_export 
				   outfile="/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/&filedate._',seller_name,'.xlsx" 
				   DBMS=XLSX REPLACE;
				   sheet="','LOANLIST','"; 
				   run;'
			)	);
run;