DATA Step, Macro, Functions and more

Read invoice number from filename

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Read invoice number from filename

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):

 

Monthinvoice_no
Jan4567
Feb4678

 

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

 

Thanks for your help.

 

 


Accepted Solutions
Solution
‎09-27-2017 10:38 AM
Super User
Super User
Posts: 7,050

Re: Read invoice number from filename

[ Edited ]

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;

 

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Read invoice number from filename

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.

Contributor
Posts: 27

Re: Read invoice number from filename

[ Edited ]

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;
Solution
‎09-27-2017 10:38 AM
Super User
Super User
Posts: 7,050

Re: Read invoice number from filename

[ Edited ]

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;

 

Contributor
Posts: 27

Re: Read invoice number from filename

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

Frequent Contributor
Posts: 149

Re: Read invoice number from filename

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.
Contributor
Posts: 27

Re: Read invoice number from filename

Posted in reply to error_prone

Thanks @error_prone for the right direction

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 100 views
  • 2 likes
  • 4 in conversation