BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ody
Quartz | Level 8 Ody
Quartz | Level 8

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Add the keyword trimmed to your select into clause

 

select a.File into : Recent trimmed

 

PG

View solution in original post

6 REPLIES 6
ballardw
Super User

I do not see where you are combining FILE and Extension.

I would try

File_Extension = cats(file,'.', extension);

PGStats
Opal | Level 21

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!

PG
Ksharp
Super User

Try informat $VARYING.

 

filename File_Dir pipe "dir &Dir";

data File_List;
infile File_Dir length=len lrecl=1000;

input folderlist $varying1000. len ;
.............
run;
Ody
Quartz | Level 8 Ody
Quartz | Level 8

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;
PGStats
Opal | Level 21

Add the keyword trimmed to your select into clause

 

select a.File into : Recent trimmed

 

PG
Ody
Quartz | Level 8 Ody
Quartz | Level 8
Verified. Thanks.

That was killing me.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2107 views
  • 0 likes
  • 4 in conversation