CODEWITHSUNDEEP
excelsassas-macro
Azeem112
Azeem112

Reputation: 377

How to convert excel file from SASContenrServer to dataset in SAS

I have some excel files in SASContentServer that I want to convert into dataset.

this is the code that I'm using

*filename out "/app/sas/test.xlsx";
filename out temp;

%let model_rk_val = "10013";
%let model_id_val = "model";

proc http 
method="get"
url="http://server:port/SASContentServer/repository/default/sasdav/Products/SASModelRisk/ModelRiskMgtMid-Tier/Content/customObject1/&model_rk_val/&model_id_val..xlsx"
webUserName="sasmrmad"
webPassword="xxxxxxx"
out=out;
run;

*libname mip_in "/app/sas/folder";

proc import datafile = out
    DBMS=xlsx
    out=mip_in.vars;
    sheet='vars';       
RUN;

proc import datafile = out
    DBMS=xlsx
    out=mip_in.outest;
    sheet='outest';     
RUN;

if i use filename out temp then system gives me error that tempfile.xlsx is missing because filename out temp crates a temporary file in temp folder without any extension and DBMS=xlsx looks for a file with extention of .xlsx and because my temp file has no extension it gives error.

if i create a file like test.xlsx and use filename out "/app/sas/test.xlsx";system says that file is damaged even if I create a new excel file there the error is same that file is damaged or file format or extension is not valid.

Kindly can you tell me that how can i resolve this issue or is there any other approach that I can use to convert my excel files to dataset?

Upvotes: 0

Views: 379

Answers (1)

cjdinger
cjdinger

Reputation: 139

Try this instead of FILENAME TEMP to create a temp file with a specific extension:

    filename out "%sysfunc(getoption(WORK))/test.xlsx";

And check out the HTTP_TOKENAUTH option instead of using webUsername and webPassword.

It's not always obvious when the HTTP call fails. Check the value of &SYS_PROCHTTP_STATUS_CODE macro variable to see that you get the expected "200" code as well (SAS 9.4M3 and later). Or use the HEADEROUT= option to write the header response details to a file that you can check.

Upvotes: 1

Related Questions