Creating a macro that reads CSV

[Jeg123]

Im trying to create a macro that has two arguments and then reads a CSV. Can somebody help me what I'm doing wrong?

%macro read_csv(csv_location, csv_file);

%let file_path = cats(&csv_location, &csv_file);
proc import datafile= &file_path;
out=test
dbms=csv
replace;

run;
%mend read_csv;

%read_csv("/file_folder/", "csv_name.csv");

[japelin · Accepted solution]

First, since cats is a function that can only be used within the data step, change it as follows

data _null_;
  call symputx('file_path',cats(&csv_location, &csv_file));
run;

Then, the semicolon after proc import datafile= is unnecessary.
Also, double quotation marks are necessary because we are concatenating the filepath string in the data step.

proc import datafile= "&file_path"

Finally, let's first create the code that works correctly without macro variables before starting macro coding.

The following options are useful for debugging macros Please refer to them.

options mprint mlogic symbolgen;

[Jeg123]

Thanks for the explanation, worked great, thanks!

[ballardw]

Strongly suggest if you are going to be importing files this way that you add a GUESSINGROWS statement with either Max or large value like 30000. Otherwise proc import only uses a few rows to set variable properties like type, length, informat and format. So if you have a variable not always used you may end up with a one character field because the first few rows had missing values. Or if a column has a mix of all digit values and letter +digits, like part numbers, if the first rows contain all digits the imported variable can be numeric and all of the values with letters will be 'invalid data' and have missing values.

Or if you have a text field such as comment the length may be set shorter than needed.

Last, if many of these CSV are supposed to be of the same structure and values you are way better off writing a data step to ensure that all of the variable properties are set the same. Otherwise you will likely have to "fix" data, on a set-by-set basis when you go to combine data for analysis or reporting.