DATA Step, Macro, Functions and more

Reading multiple .csv files with different variables by using SAS macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Reading multiple .csv files with different variables by using SAS macro

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;

 


Accepted Solutions
Solution
‎10-11-2017 11:56 AM
Super User
Posts: 13,023

Re: Reading multiple .csv files with different variables by using SAS macro

Posted in reply to brainupgraded

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


All Replies
Super User
Posts: 22,844

Re: Reading multiple .csv files with different variables by using SAS macro

Posted in reply to brainupgraded

 

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.

Solution
‎10-11-2017 11:56 AM
Super User
Posts: 13,023

Re: Reading multiple .csv files with different variables by using SAS macro

Posted in reply to brainupgraded

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.

Occasional Contributor
Posts: 5

Re: Reading multiple .csv files with different variables by using SAS macro

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! Smiley Happy

 

 

%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));

 

Super User
Super User
Posts: 7,847

Re: Reading multiple .csv files with different variables by using SAS macro

[ Edited ]
Posted in reply to brainupgraded

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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 285 views
  • 2 likes
  • 4 in conversation