topic Re: macro importex error in SAS Programming

macro importex error

wlierman — Wed, 22 Apr 2020 22:57:33 GMT

I am trying to import a small csv file into SAS using the following code.

However, I receive an error. I have copied the error code after the SAS statements. There are a lot of superfluous lines in the error log.

Thank you for your help.

%macro importex(filen,datan,sheetnam);

proc import out=&datan

datafile="&filen"

dbms=excel

replace;

%if &sheetnam ^= %then %do; sheet="&sheetnam"; %end;

run;

%mend importex;

%importex(I:\Health Analytics\DRTS_TEMP_WL\NDC_CODE_HIV.csv, M:\DATA\OR0206965\DRTS_Requests\Req_5315_HIV_AIDS_PROVIDER_INFORMATION\NDC_CODE_HIV.SAS, NDC_CODE_HIV);

%macro importex(filen,datan,sheetnam);

63 63 63 63 63 63

3846 proc import out=&datan

63 63 63 63

3847 datafile="&filen"

63 63

3848 dbms=excel

63 63

3849 replace;

63 63

3850 %if &sheetnam ^= %then %do; sheet="&sheetnam"; %end;

63 63 63 63 63 63 63 63 63

3851 run;

3852 %mend importex;

63 63 63

3853 %importex(I:\Health

63 63 63 63 63 63 63 63 63 63 63

63 63

3853! Analytics\DRTS_TEMP_WL\NDC_CODE_HIV.csv,M:\DATA\OR0206965\DRTS_Requests\Req_5315_HIV_AIDS_PROVID

3853! ER_INFORMATION\NDC_CODE_HIV,NDC_CODE_HIV);

ERROR 63-169: The word currently being processed has exceeded the maximum length of 0 characters.

Re: macro importex error

Tom — Wed, 22 Apr 2020 23:30:34 GMT

The code you posted would not generated that error. You probably had a previous unbalanced quote. Restart SAS and try again.

But there are a couple of coding errors in that program.

First the PROC IMPORT code the macro is trying to generate is wrong. You cannot use the EXCEL engine to read a CSV file. Use the CSV or DLM engine instead. And there is no SHEETNAME option with a CSV file.

Second the macro wants a dataset name (which you could replace with a quoted physical SAS dataset filename) for the parameter DATAN. But you have called it with an unquoted physical filename. Plus the filename is to a SAS program file, not a SAS dataset name.

Assuming that you do want to try reading the CSV file with PROC IMPORT you really don't need to create a macro to do that. Just write the code directly.

proc import out=work.NDC_CODE_HIV replace
      datafile="I:\Health Analytics\DRTS_TEMP_WL\NDC_CODE_HIV.csv"
      dbms=csv    
;
run;

But you might want to look at that program file. Perhaps it has actual SAS code to read the CSV file, instead of forcing SAS to guess what is in the CSV.

Re: macro importex error

ChrisNZ — Thu, 23 Apr 2020 21:17:00 GMT

%macro importex(filen,datan,sheetnam);

proc import out=&datan

So the data set name is the second parameter?

If you write the code in a more legible manner (and you always should, there is no excuse) you see this:

%importex( I:\Health Analytics\DRTS_TEMP_WL\NDC_CODE_HIV.csv

, M:\..DRTS_Requests\Req_5315_HIV_AIDS_PROVIDER_INFORMATION\NDC_CODE_HIV.SAS

, NDC_CODE_HIV );

The second parameter is not a data set name.

Re: macro importex error

wlierman — Thu, 23 Apr 2020 14:03:36 GMT

Thank you Chris for your assistance in solving this issue.Appreciate it very much.
Walt Lierman

Re: macro importex error

wlierman — Thu, 23 Apr 2020 18:21:33 GMT

Hi Tom,

Thank you again for your time and knowledge to solve this proble.

Walt Lierman