DATA Step, Macro, Functions and more

Dynamic Variable Length

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 83
Accepted Solution

Dynamic Variable Length

[ Edited ]

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. 


Accepted Solutions
Solution
‎07-14-2016 11:18 AM
Respected Advisor
Posts: 4,646

Re: Dynamic Variable Length

Add the keyword trimmed to your select into clause

 

select a.File into : Recent trimmed

 

PG

View solution in original post


All Replies
Super User
Posts: 10,500

Re: Dynamic Variable Length

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

I would try

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

Respected Advisor
Posts: 4,646

Re: Dynamic Variable Length

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
Super User
Posts: 9,681

Re: Dynamic Variable Length

Try informat $VARYING.

 

filename File_Dir pipe "dir &Dir";

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

input folderlist $varying1000. len ;
.............
run;
Frequent Contributor
Frequent Contributor
Posts: 83

Re: Dynamic Variable Length

[ Edited ]

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;
Solution
‎07-14-2016 11:18 AM
Respected Advisor
Posts: 4,646

Re: Dynamic Variable Length

Add the keyword trimmed to your select into clause

 

select a.File into : Recent trimmed

 

PG
Frequent Contributor
Frequent Contributor
Posts: 83

Re: Dynamic Variable Length

Verified. Thanks.

That was killing me.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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