BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dayuan
Obsidian | Level 7

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

The file also has the quotation marks?

View solution in original post

15 REPLIES 15
PGStats
Opal | Level 21

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)

PG
dayuan
Obsidian | Level 7

Hi PG,

 

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

dayuan
Obsidian | Level 7

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
Super User

The file also has the quotation marks?

dayuan
Obsidian | Level 7

Hi PG, 

 

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

Reeza
Super User

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
Super User

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
Jade | Level 19

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
Obsidian | Level 7

Hi Reinhard

 

LinusH
Tourmaline | Level 20
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...?
Data never sleeps
dayuan
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Jade | Level 19

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 1581 views
  • 8 likes
  • 7 in conversation