topic Re: Importing previous day Excel file with date Macro in SAS Programming

Importing previous day Excel file with date Macro

Yvegunta — Tue, 19 Jul 2016 04:37:23 GMT

Hi All,

I am trying to import an Excel file (.xlsx), which currently sits on the server.

The query i have been trying to run is as below:

Proc import datafile=New_File_&Pdate..xlsx (&Pdate:is a macro assigned for previous day)

out=Prev_File

dbms=XLSX;

Getnames=Yes;

run;

However, i have been getting this error:

16 Proc import datafile=New_File_&Pdate..xlsx

NOTE: Line generated by the macro variable "PDATE".

16 New_File_18JUL2016

_________________

201

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string.

ERROR 201-322: The option is not recognized and will be ignored.

Can you please help me understand, what the error is.

Thank you

Re: Importing previous day Excel file with date Macro

Ksharp — Tue, 19 Jul 2016 07:07:20 GMT

You need put double QUOTE around it .

Proc import datafile="c:\temp\New_File_&Pdate..xlsx" replace

out=Prev_File

dbms=XLSX;

Getnames=Yes;

run;

Re: Importing previous day Excel file with date Macro

DJongman — Tue, 19 Jul 2016 07:09:39 GMT

I can't explain exactly your error, but tested the next code below and that is working:

%let pdate =  %sysfunc(putn(%eval(%sysfunc(today())-1),date9.));

Proc import out = work.xlsdat
	datafile = "c:\New_File_&pdate.xlsx"
	dbms = xlsx replace;
	getnames = yes;
run;

Maybe you can use this for you own macro.

Re: Importing previous day Excel file with date Macro

Yvegunta — Mon, 25 Jul 2016 03:04:04 GMT

Thanks DJongman.

I used your method and tweaked it a bit and it was the exact output i wanted.

Thanks again.

Re: Importing previous day Excel file with date Macro

Yvegunta — Mon, 25 Jul 2016 03:04:35 GMT

Thank you KSharp.