Hello all,
I'm reading file names into a dataset from a directory:
filename File_Dir pipe "dir &Dir";
data File_List;
infile File_Dir truncover;
input folderlist $char1000.;
length
folder file $75.
extension $6.
date 8;
retain folder;
if index(folderlist,'Directory') > 0 then folder = substr(folderlist,14,180);
if scan(folderlist,2,'.') ne ' ' then do;
file = strip(scan(substr(folderlist,40,160),1,'.'));
extension = upcase(scan(strip(folderlist),-1,'.'));
date = mdy(input(substr(folderlist,1,2),8.),input(substr(folderlist,4,2),8.),input(substr(folderlist,7,4),8.));
size = input(strip(substr(folderlist,30,10)),comma32.);
end;
if date ne .;
if findc(extension,'~%%$') = 0;
keep Folder File Extension Date ;
format Date mmddyy10.;
run;
My problem is that no matter how I try to strip away blank spaces from the File name they stay at a length of 75. This is creating file name issues later on when I try to export updated versions of the files using the same name.
I'm getting something like "Example File Name .xlsx"
I've tried strip, compress, trim, etc and cant seem to figure out how to get rid of that extra space. I can manully change the length of the File variable but that's defeating the purpose. Is there a way to dynamically change the length of the File variable to the max length of File?
Appreciate the thoughts. thanks.
Add the keyword trimmed to your select into clause
select a.File into : Recent trimmed
I do not see where you are combining FILE and Extension.
I would try
File_Extension = cats(file,'.', extension);
SAS character strings have a fixed length. Saying
str = trim(str);
does nothing.
You must do the trim of strip within the expression where you concatenate the substrings.
Concatenation functions CATS, CATX and CATT do that automatically for you. Check them out!
Try informat $VARYING.
filename File_Dir pipe "dir &Dir";
data File_List;
infile File_Dir length=len lrecl=1000;
input folderlist $varying1000. len ;
.............
run;
Sorry if I wasnt clear in my initial post.
Once I pull the diretory file names into a data set I take the most recenetly added file, grab the name and then the file itself:
proc sql noprint;
select a.File into : Recent
from File_List a
left join (select Max(Date) as Date format=mmddyys10.
from File_List where extension = "XLSX") b
on a.Date = b.Date
where (a.Date = b.Date);
quit;
%put &Recent;
proc import
out = CCR
datafile = "&Dir&Recent"
dbms = xlsx replace;
getnames = YES;
run;
After some modifications and field additions the file is then exported.
It's at this time that I'm getting the extra spaces in the file name. "Example File Data .xlsx"
No matter what I seem to do I can't get rid of it. The field is as long as the value I specify on my length statement when I input the directory information. I tried using $Varying1000. as a format but it was giving me errors.
I'm using 9.4.
proc export
data = TCL
outfile = "&output\Updated_&Recent..xlsx"
DBMS = EXCEL replace;
SHEET = "data";
RUN;
Add the keyword trimmed to your select into clause
select a.File into : Recent trimmed
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: