topic Re: Dynamic Variable Length in SAS Programming

Dynamic Variable Length

Ody — Wed, 13 Jul 2016 21:14:17 GMT

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.

Re: Dynamic Variable Length

ballardw — Wed, 13 Jul 2016 21:20:55 GMT

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

I would try

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

Re: Dynamic Variable Length

PGStats — Wed, 13 Jul 2016 21:22:36 GMT

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!

Re: Dynamic Variable Length

Ksharp — Thu, 14 Jul 2016 04:42:14 GMT

Try informat $VARYING.

filename File_Dir pipe "dir &Dir";

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

input folderlist $varying1000. len ;
.............
run;

Re: Dynamic Variable Length

Ody — Thu, 14 Jul 2016 13:38:19 GMT

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;

Re: Dynamic Variable Length

PGStats — Thu, 14 Jul 2016 13:55:20 GMT

Add the keyword trimmed to your select into clause

select a.File into : Recent trimmed

Re: Dynamic Variable Length

Ody — Thu, 14 Jul 2016 15:18:35 GMT

Verified. Thanks.

That was killing me.