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

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

6 REPLIES 6
ballardw
Super User

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
Obsidian | Level 7

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
Super User Tom
Super User

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
Obsidian | Level 7

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

error_prone
Barite | Level 11
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
Obsidian | Level 7

Thanks @error_prone for the right direction

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
  • 1132 views
  • 2 likes
  • 4 in conversation