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

Hello,

 

I am trying to read many csv files saved in one folder, and I am using the code below.

But I keep getting an error message:

"ERROR: Physical file does not exist, C:\Users\shan32\Desktop\Sean\GDrive\Research\SHESC\S8V and
Food Environment\Analysis\DR.
ERROR: Import unsuccessful. See SAS Log for details."

 

1) The path in the error is different from path1 (see the code). I am not sure why.

2) I still cannot understand what "filrf=mydir" does. I am confused because of the following line assigning dir name. 

3) Is there any way I can save datasets with different names, such as "age", "sex", "employment", etc, instead of file1, file2, ... fileX?

 

Please help!

 

 

%let path1=C:\Users\shan32\Desktop\Sean\GDrive\Research\SHESC\S8V and Food Environment\Data\ACS\All 2007-2015\ANN\;

%macro myimport;
%let filrf=mydir;
%let rc=%sysfunc(filename(filrf,"&path1\")); /* assign dir name */
%let did=%sysfunc(dopen(&filrf)); /* open directory */
%let lstname=; /* clear filename macro var */
%let memcount=%sysfunc(dnum(&did)); /* get # files in directory */

%if &memcount > 0 %then 
	%do i=1 %to &memcount; 
		%let lstname=%sysfunc(dread(&did,&i)); 
		*%let censusname=%scan(&censusfile,&i); 

		filename dr "&path1\&lstname"; 
			proc import out=file&i 
				datafile=dr dbms=csv replace;
				getnames=yes;
				datarow=3;
			run;
	%end;

%let rc=%sysfunc(dclose(&did)); /* close directory */
%mend myimport;

%myimport;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

It probably doesn't help that you define Path1 as ending in \ and then use it as &path1.

Look in the log to see what happens with this code:

%let path1=C:\Users\shan32\Desktop\Sean\GDrive\Research\SHESC\S8V and Food Environment\Data\ACS\All 2007-2015\ANN\;;

%let j="&path1\"; 
%put &j;

You actual file system probably doesn't like the \\ at the end of the actual path.

 

And so the compound names with the files don't actually exist.

 

Since it isn't clear if you actually copied the entire code and error from the log correctly I'm not sure why the displayed value has a shorter path. Best is to copy actual code and error together from the log and paste that into a code box here opened with the forum {I} menu icon.

 

You may have other items in that directory, such as other directories or non-CSV files which seems possible given that the example error doesn't include a .csv extension. Your code attempts to import them as well since you did not provide an filter on the file extension.

 

%let filrf=mydir;  in this context expects that you have already established a file reference to a folder using a filename statement such as:

filename mydir "C:\path\folder\";

 

If you want to use a different output file name then you will have to have some way to associate the desired text with the correct file imported. If your &lstname variable contains the text in consistent manner you could extract it from the name. If not you have more work cut out. You'll have to describe the actual csv filename and the SAS data set name you want to get a more detailed answer.

View solution in original post

4 REPLIES 4
Reeza
Super User

 

1) The path in the error is different from path1 (see the code). I am not sure why.

 

Post the error perhaps?

 

2) I still cannot understand what "filrf=mydir" does. I am confused because of the following line assigning dir name. 

 

It appears to be a path definition. In the future comment your code so you understand what things are.

 

3) Is there any way I can save datasets with different names, such as "age", "sex", "employment", etc, instead of file1, file2, ... fileX?

 

Change the OUT to be the name of you want. You can add a parameter to the macro.

 

 

1. Make sure your code works BEFORE you convert it to a macro. 

2. Separate your process to make it more clear. First get your list of files, second, get the names of the data set you want. Third, pass it to a  macro to import the file.

3. If you have issues post your code and log.

ballardw
Super User

It probably doesn't help that you define Path1 as ending in \ and then use it as &path1.

Look in the log to see what happens with this code:

%let path1=C:\Users\shan32\Desktop\Sean\GDrive\Research\SHESC\S8V and Food Environment\Data\ACS\All 2007-2015\ANN\;;

%let j="&path1\"; 
%put &j;

You actual file system probably doesn't like the \\ at the end of the actual path.

 

And so the compound names with the files don't actually exist.

 

Since it isn't clear if you actually copied the entire code and error from the log correctly I'm not sure why the displayed value has a shorter path. Best is to copy actual code and error together from the log and paste that into a code box here opened with the forum {I} menu icon.

 

You may have other items in that directory, such as other directories or non-CSV files which seems possible given that the example error doesn't include a .csv extension. Your code attempts to import them as well since you did not provide an filter on the file extension.

 

%let filrf=mydir;  in this context expects that you have already established a file reference to a folder using a filename statement such as:

filename mydir "C:\path\folder\";

 

If you want to use a different output file name then you will have to have some way to associate the desired text with the correct file imported. If your &lstname variable contains the text in consistent manner you could extract it from the name. If not you have more work cut out. You'll have to describe the actual csv filename and the SAS data set name you want to get a more detailed answer.

brainupgraded
Obsidian | Level 7

Thank you so much. It seems like the main issue was "mydir". I didn't establish a file reference, but it was in the code I copied from without any explanation. 

 

As you directed,

1) I removed "\" in path1

2) and re-defined mydir by using a filename function

 

Now, it works! 🙂

 

 

%let path1=C:\Users\shan32\Desktop\Sean\GDrive\Research\SHESC\S8V and Food Environment\Data\ACS\All 2007-2015\ANN\2009;
%let mydir=%sysfunc(filename(&path1));

 

Tom
Super User Tom
Super User

The FILENAME function needs you to supply both the path for the fileref and the name for the fileref. The value it returns is the status of whether the function worked.  So the code you posted. 

%let path1=C:\Users\shan32\Desktop\Sean\GDrive\Research\SHESC\S8V and Food Environment\Data\ACS\All 2007-2015\ANN\2009;
%let mydir=%sysfunc(filename(&path1));

should set MYDIR to an error code.

1016  %let mydir=%sysfunc(filename(&path1));
1017  %Put &=mydir;
MYDIR=10005

If you want to define the fileref MYDIR to point to the directory named in macro variable PATH1 then why not just use a FILENAME statement instead of trying to figure out the syntax of the FILENAME function?

filename mydir "&path1";

 If you want to use the FILENAME function and have it pick a fileref to use and return the value of the fileref it generated to the macro variable mydir then set mydir to empty and pass mydir as the value of the fileref parameter in the function call.

1021  %let mydir=;
1022  %let rc=%sysfunc(filename(mydir,&path1));
1023  %Put &=rc &=mydir;
RC=0 MYDIR=#LN00072

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
  • 4 replies
  • 2019 views
  • 2 likes
  • 4 in conversation