Read invoice number from filename

[PBsas]

Hello SAS Gurus,

I need help to extract number from a invoice file the vendor sends every month and have it as a variable.

invoice_01jan2017_31Jan2017_4567.xlsx --> January file

invoice__01feb2017_28Feb2017_4678.xlsx  --> feburary file

What I need is(in addition to bunch of fields within the file):

Month	invoice_no
Jan	4567
Feb	4678

I need to prgrammatically read the invoice number from the filename every month.

Thanks for your help.

[ballardw]

How do you read the data? And what is the name after you read it into SAS? Code examples would be the best way to answer.

I ask because there are a number of ways that may be very easy depending on the answers to these questions.

[PBsas]

Hi @ballardw,

This is what I have been using to import the file right now. But I am not able to read the invoice number.

data _null_;
	day=today();
	call symput("begmnth",put(intnx("month",day,-8,'b'),date9.));
run;

%put &=begmnth.;

filename invoice "&path./invoice_&begmnth.*";

proc import out= want 
  datafile=invoice dbms= xlsx replace;
  startrow=3; 
  Sheet = 'Sheet1'; 
  Getnames = No; 
run;

[Tom · Accepted solution]

So if you have already have a pattern for finding the filename then you could use that to also get the actual filename and parse out the invoice part as save it as a macro varaible. You could also save the physical filename as a macro variable and use that directly in the IMPORT step instead of the fileref.

data _null_;
   length filename $256 ;
   infile "&path./invoice_&begmnth.*" filename=filename ;
   input @;
   invoice = scan(filename,-2,'._');
   call symputx('filename',quote(trim(filename));
   call symputx('invoice',invoice);
   stop;
run;

Which you can then use in your other code to read the data. For example you could add a data step to add variables with the invoice number and beginning date.

proc import out= want replace
  datafile=&filename dbms= xlsx ;
  startrow=3; 
  Sheet = 'Sheet1'; 
  Getnames = No; 
run;

data want ;
   invoice=&invoice ;
   begmnth= "&begmnth"d ;
   format begmnth date9. ;
   set want;
run;

[PBsas]

Thank you @Tom This is exactly what I was looking for.

[error_prone]

You can use the scan function to extract the invoice number from the filename. Within a data step:
invoice_no = scan(filename_var, 4, "_.");

Depending on what you already have, a better solution may exist.

[PBsas]

Thanks @error_prone for the right direction