macro importex error

[wlierman]

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;

      63

         63

3852  %mend importex;

       63   63      63

      63

3853  %importex(I:\Health

       63      63  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.

[Tom]

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.

[wlierman]

Hi Tom,

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

Walt Lierman

[ChrisNZ]

%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.

[wlierman]

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