07-13-2016 05:10 PM - edited 07-13-2016 05:14 PM
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.
07-13-2016 05:22 PM
SAS character strings have a fixed length. Saying
str = trim(str);
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!
07-14-2016 12:42 AM
Try informat $VARYING.
filename File_Dir pipe "dir &Dir"; data File_List; infile File_Dir length=len lrecl=1000; input folderlist $varying1000. len ; ............. run;
07-14-2016 09:37 AM - edited 07-14-2016 09:38 AM
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;
Need further help from the community? Please ask a new question.