@YKU wrote:
Hi Ballardw
Thanks for your help, I modified a few things to get it run:
1. call execute ("Proc import datafile=") --> somehow I have to use datafile instead data to run
2. outname= catx('.','work',scan(file_name,2,'./-','b')); ---> This is where I get error I guess
The file_name is very long and its names are identical in the last parts. So I keep getting error as "The value 31 is not a valid SAS name."
The sample of the original filename:
PPP_Subscribers_APPLICATE-ACE_SOMETable_2016-01-01_2016-12-31
PDC_Subscribers_CHOOACE-ACE_OTHERTable_2016-01-01_2016-12-31
I tried other ways to change the output name but cannot find a valid way to do it.
Can you help me? Thanks
My mistake on the datafile , typos happen.
Remember that I suggested adding the desired SAS data set names to your input data file incase of long names. Since you did not provide an example of an actual input file I provided a solution that might work. With that sort of actual name you have I strongly suggest that you add a desired name to the data file.
SAS data sets have a hard limit of 32 characters at the set name level. The name of a data set must start with either a letter or the underscore , _ , character. The SCAN code I provided was to use the slashes to delimit the path elements and the period to delimit the CSV extension so we could get the last "word" between \ and . The 'b' modifier is to parse backwards, or from the end. But YOU added a - which gets the LAST - resulting with a word, 31 in the example, that is not a valid SAS data set name.
I suggest that you manually create a name for each of your data sets.
I am not sure what the best way would be. How did you get the data set with the files to begin with?
Easiest may be to make a new dataset with the desired data set names and merge them with the original file names. Here is an example:
data work.filenames;
input file_name :$40.;
datalines;
c:\folder\subfolder\name.csv
c:\folder\subfolder\othername.csv
;
data work.datasetname;
input dsname :$32.;
datalines;
Firstdataset
Secondsdataset
;
data work.file_datasets;
merge work.filenames
work.datasetname
;
run;
You could add the library name to the dsname values using values like libname.setname. Make the dsname varaiable 41 using the input statement and the $41. informat.
The : in the input format helps to avoid, in this case, continuing to read from the next line in case your actual value is shorter.
The MERGE done this way matches the first line of the first data set with the second. So you would want to make sure the order matches.
Then use the work.file_datasets or whatever you create as input to the data _null_. Use the created the DSNAME value instead of the Scan code reading using the file_name variable.
Alternatively be unselective about taking the first 32 characters but if those are not unique then you will overwrite one or more data sets.
This demonstrates a way to get the first 32 characters and prefix with a library.
data work.junk;
file_name="c:\folder\PPP_Subscribers_APPLICATE-ACE_SOMETable_2016-01-01_2016-12-31.csv";
length outname $ 41;
outname= catx('.','work',substr (scan(file_name,2,'.\','b'),1,32));
run;
In this case if the name after the last \ and before the period is shorter than 32 this is not quite correct because the string is too short for the SUBSTR function. You would get an outname value that may be workable but there will be notes in the log about "Invalid third argument to function SUBSTR".
Again if these won't be unique then you have to provide the name or a much cleaner example of the CSV file names and rules for creating your desired dataset name.
