where is my code of importing cvs file into SAS wrong

[dayuan]

Hi All,

I need to import hundreds of cvs. files with title of specific company names into sas. I currently have a complete company name list and try to write a macro code to import a cvs file with the relevant company name into sas every time. For example, my first company obs. in my list is "IBM", so I need to proc import a relevant cvs. file also named with "IBM". Currently, my code is as follows:

%macro m1(name=); 
proc import datafile='D:\DATA\company\&name.csv' out=&name dbms=csv replace;run;
%mend m1;

data _null_;
set company_name_list;
call symput('name'|| put(_n_,1.), company name);
run;

%macro m2;
%local j;
%do j = 1 %to 500;
%m1(name=&&name&j);

%end;
%mend m2;
%m2;

However, the log page suggests that:

ERROR: Physical file does not exist, D:\DATA\company\&name.csv.
ERROR: Import unsuccessful. See SAS Log for details.

So I would really appreicate it if any one could help me out. 

[PGStats]

Try a simpler approach:

data _null_;
length line $256;
set company_name_list;
line = catt(
    "proc import datafile='D:\DATA\company\", 
    company_name,
    ".csv' out=",
    compress(company_name),
    " dbms=csv replace; run;");
call execute(line);
run;

(untested)

[dayuan]

Hi PG,

I would say "call execute(line)" is really powerful!

[dayuan]

Hi PG,

I am using your code and call execute is very efficient, but the thing is when there is a single quotation mark in company name, the code fails. I try to fix the code by replacing the single quotation marks after "datafile" with double quotation marks, but it does not work. I would really appreciate it if you could give me a help.

[Reeza · Accepted solution]

The file also has the quotation marks?

[dayuan]

Hi PG, 

Thanks for your quick response. Yes, the file also has single quotation marks.

[Reeza]

The problem with your code is likely some extra spaces in your macro variable, as well as your data step to create macro variables is incorrect. 

1. Change to call symputX to remove trailing spaces. 

2. Change variable to company_name, since company name would likely generate an error as it's not a valid SAS name.

data _null_;
set company_name_list;
call symputx('name'|| put(_n_,1.), company_name);
run;

3. Check what your macro variable is resolving to, it may not be what you expect.

%put Original - &&name&j;

%put Modified - &&&name&j;

And last but not least, if the files have the same structure and are in the same folder you can consider another option:

4. Use a wildcard and a data step to read all the files at once into a single SAS data set with a variable to identify the source dataset.

Here's a bit of a write up I wrote on avoiding a macro to accomplish this.

data import_all;

*make sure variables to store file name are long enough;
length filename txt_file_name $256;

*keep file name from record to record;
retain txt_file_name;

*Use wildcard in input;
infile "Path\*.txt" eov=eov filename=filename truncover;

*Input first record and hold line;
input@;

*Check if this is the first record or the first record in a new file;
*If it is, replace the filename with the new file name and move to next line;
if _n_ eq 1 or eov then do;
 txt_file_name = scan(filename, -1, "\");
 eov=0;
end;

*Otherwise  go to the import step and read the files;
else input

 *Place input code here;

;
run;

[Kurt_Bremser]

First of all:

DO NOT USE BLANKS IN FILENAMES!

Print the above in at least 48 points and stick it on top of your computer monitor. Also supply your workmates with this important rule.

Although it is possible to use blanks, they only cause you grief along the way, repeatedly. Use underlines where a visual separator is needed.

As soon as you have blanks in filenames, you cannot use the same name for SAS datasets, as valid SAS names need to consist of characters, numbers and the underline character only.

When using a macro variable within a string (like you did with &name.csv), you need to delimit the macro variable reference with a dot. If you need a dot to be inserted, then you have to write two dots.

If &name is xxx, then &name.csv will resolve to xxxcsv; &name..csv will resolve to xxx.csv.

[FreelanceReinh]

Another mistake was, that macro variable reference &name is not resolved inside of single quotes (see first error message). You should have used double quotes around the path. The first error message would then have complained about a non-existing file such as IBMcsv, thus making you aware of the missing second dot in "&name.csv" (see @Kurt_Bremser's post).

[dayuan]

Hi Reinhard,

Thank you so much for your reply. the code now works!

[LinusH]

Wouldn't the simplest be to do call execute instead of going the extra logic of having an array style macro variable and separate macro call...?

[dayuan]

HI LinusH,

I thank your reply. As the first reply by PG suggests, call execute is definitely powerful and saves time. but I need to change the each file firstly after importing it and then combine them into a final dataset. I can figure them out using macro, but I might try to find out how to realize it with call execute. anyway, I appreciate your reply!

[ballardw]

You also don't allow enough macro variable names to begin with"

call symput('name'|| put(_n_,1.), company name); <= This line limits you to variables Name1 , Name2 ... Name9 and then start generating Name* as putting _n_ > 9 with a 1 space format just doesn't fit.
run;

%macro m2;
%local j;
%do j = 1 %to 500; 
%m1(name=&&name&j); <= and here you use way more values of Name then set above PLUS assuming the there exactly 500 names to use. 

[dayuan]

Hi Ballardw,

Thank you so much for the reminder.  I took your advice and have changed the relevant code into

"call symput('name'|| put(_n_,3.), company name)"

[FreelanceReinh]

---

@dayuan wrote:

Hi Ballardw,

Thank you so much for the reminder.  I took your advice and have changed the relevant code into

"call symput('name'|| put(_n_,3.), company name)" 

---

Hi @dayuan,

I assume you haven't run the above code yet, because it wouldn't work for _n_<100 due to the right-alignment of these values when formatted with the 3. format. (It wouldn't work for _n_>=100 either because of the invalid variable name "company name", as others have mentioned earlier.) To correct this specific line of code, I would suggest:

call symputx(cats('name', _n_), company_name);

(incorporating @Reeza's suggestion of using CALL SYMPUTX).

The CATS function avoids any blanks between "name" and the numeric suffix. It doesn't cause notes about numeric-to-character conversion in the log. Moreover, this code would work even if _n_ exceeded 999.