topic Re: Rename multiple csv files before converting them to SAS in SAS Enterprise Guide

Rename multiple csv files before converting them to SAS

Sean_OConnor — Wed, 24 Feb 2021 17:10:05 GMT

Hi All,

I have a folder which contains multiple csv files - 30+

I have a macro which reads in the list of files and processes them and converts into sas one by one.

However, one of the steps I need to do is manually change the csv file name before I run them. I find this tedious and time consuming. Basically before I convert the files to SAS I would like to rename each file.

This is an example of the location of the file and naming convention before the manual intervention.

//collect/Migration/Datasets/ABC/123a/Processing/just_in_daily_submission_file.2102010610.csv
//collect/Migration/Datasets/ABC/123a/Processing/just_in_daily_submission_file.2102020702.csv

This what it looks like after I do the manual name change.

//collect/Migration/Datasets/ABC/123a/Processing/ABC_2021_02_01.csv
//collect/Migration/Datasets/ABC/123a/Processing/ABC_2021_02_02.csv

So you can see there is a pattern there. All the non numeric parts of the filename gets changed to 'ABC'.

Also the date before the manual intervention is yymmdd followed by random numbers.

I manually change this to yyyy_mm_dd.

Would anyone have thoughts I could automate such a step in SAS?

Many thanks,

Sean

Re: Rename multiple csv files before converting them to SAS

data_null__ — Wed, 24 Feb 2021 17:26:13 GMT

Aren't your "random numbers" time of day? Seems like you would want to keep that part.

Re: Rename multiple csv files before converting them to SAS

Reeza — Wed, 24 Feb 2021 17:28:48 GMT

FCOPY to copy the file/rename it.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lefunctionsref&docsetTarget=n10dz22b5ixohin1vwzilweetek0.htm&locale=en

Use FDELETE() to delete the old copy.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lefunctionsref&docsetTarget=p0h945u5r0cv6yn1u6qs35hiqt9t.htm&locale=en

FCOPY/FDELETE work the same regardless of OS so they're better than pipe commands which can be locked down and are OS dependent.

@Sean_OConnor wrote:

Hi All,

I have a folder which contains multiple csv files - 30+

I have a macro which reads in the list of files and processes them and converts into sas one by one.

However, one of the steps I need to do is manually change the csv file name before I run them. I find this tedious and time consuming. Basically before I convert the files to SAS I would like to rename each file.

This is an example of the location of the file and naming convention before the manual intervention.

//collect/Migration/Datasets/ABC/123a/Processing/just_in_daily_submission_file.2102010610.csv
//collect/Migration/Datasets/ABC/123a/Processing/just_in_daily_submission_file.2102020702.csv

This what it looks like after I do the manual name change.

//collect/Migration/Datasets/ABC/123a/Processing/ABC_2021_02_01.csv
//collect/Migration/Datasets/ABC/123a/Processing/ABC_2021_02_02.csv

So you can see there is a pattern there. All the non numeric parts of the filename gets changed to 'ABC'.

Also the date before the manual intervention is yymmdd followed by random numbers.

I manually change this to yyyy_mm_dd.

Would anyone have thoughts I could automate such a step in SAS?

Many thanks,

Sean

Re: Rename multiple csv files before converting them to SAS

Sean_OConnor — Tue, 02 Mar 2021 17:21:22 GMT

Thanks for this. I'm still slightly confused how I might do something like that in macro?

Any tips would be great

Re: Rename multiple csv files before converting them to SAS

Reeza — Tue, 02 Mar 2021 17:23:24 GMT

You first start with working code, do you have that sorted out?

Re: Rename multiple csv files before converting them to SAS

Sean_OConnor — Wed, 03 Mar 2021 13:02:14 GMT

Hi Reeza,

I have changed the method around to just read the csv file as is and rename the outputted SAS file. I nearly have it working but this is my issue.

So my &name is being put in double quotations. I think this is the only issue to get this working.

ERROR: ""File_2021_02_05"" is not a valid name.

*This is the macro used to import csv files into SAS;
%macro process(DirectoryAndDataset= ,name=);
	/*Firstly we read in the dataset called DatasetsInADirectory and keep one record each time depending on what iteration we are in*/
	data DatasetsInADirectory_1;
		set DatasetsInADirectory;
		where DirectoryAndDataset="&DirectoryAndDataset" and name="&name";
	run;

	/*Program for loading the file we get*/
	proc import datafile="&mig01.\Datasets\Files\ABC\2021\&DirectoryAndDataset..csv"
		out="&name"(label='File 123')
		dbms=csv
		replace;
		getnames=no;

		*datarow=2;
		GUESSINGROWS=MAX;
		run;

%mend process;

*The above is the macro which is used, the below is the function which carries the process out for all N datasets;

/*Put a timer at begining and end to see how long processing takes for everything*/
data macro_call;
	set DatasetsInADirectory;

	*build macro call string;
	str = catt('%nrstr(%process)(DirectoryAndDataset =', DirectoryAndDataset, ' ,name =', name,');');

	*call macro;
	call execute(str);
run;

Re: Rename multiple csv files before converting them to SAS

Kurt_Bremser — Wed, 03 Mar 2021 13:12:36 GMT

How do you build your dataset DatasetsInADirectory?

And why do you create a dataset in your macro (DatasetsInADirectory_1) that you never use?

Re: Rename multiple csv files before converting them to SAS

Sean_OConnor — Wed, 03 Mar 2021 13:20:33 GMT

Hi Kurt,

DatasetsInDirectory is built by giving me a list of all csv files in my directory I'm looking to convert to SAS.

After I do some adjustments I'm left with two columns. The first column called DatasetsInDirectory is the names of all the csv files I want to convert. The second column called name is the outputted name I want to name the SAS dataset as.

The way the program worked before is that it would read in the list of datasets in one by one and carry out the import.

This macro does what it's supposed to be when I just had this call-

*build macro call string;
	str = catt('%nrstr(%process)(DirectoryAndDataset =', DirectoryAndDataset, ');');

I'm trying to add the name part in now and I think I have the quotations in the wrong place.

Any help would be great.

Re: Rename multiple csv files before converting them to SAS

Kurt_Bremser — Wed, 03 Mar 2021 13:27:41 GMT

First of all, the OUT= option of the PROC IMPORT statement needs a dataset identifier, which is usually in the form LIBRARY.DATASET (or DATASET alone if in WORK) without quotes.

What puzzles me are the double double quotes in your ERROR message, that's why I asked how you build that dataset. Make sure that no quotes are included in the name variable.

Re: Rename multiple csv files before converting them to SAS

Kurt_Bremser — Wed, 03 Mar 2021 13:38:03 GMT

And build the call string this way:

str = catt('%nrstr(%process(DirectoryAndDataset =', DirectoryAndDataset, ',name=',name,'));');

Re: Rename multiple csv files before converting them to SAS

Sean_OConnor — Wed, 03 Mar 2021 14:01:34 GMT

Thank you very much for your help Kurt.

I seem to be getting presented with the issue that SAS is telling my csv file does not exist.

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to 
WORK.PARMS.PARMS.SLIST.
ERROR: Physical file does not exist, 
\\cmcollect01\abc\Migration_01\Datasets\123\we\Processing\daily_submission_file.210205065.csv. 
ERROR: Import unsuccessful.  See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds

However, I can navigate to the following location and find the csv file called daily_submission_file.210205065

Again, this previously worked so I'm not sure what's happening now.

Re: Rename multiple csv files before converting them to SAS

Sean_OConnor — Wed, 03 Mar 2021 14:20:58 GMT

I found the error here. My column DatasetsInDirectory was being truncated hence I wasn't picking up the full name.

Re: Rename multiple csv files before converting them to SAS

Kurt_Bremser — Thu, 04 Mar 2021 08:08:02 GMT

Take a look at Maxim 47. This happens to everybody (including me), again and again.