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.
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;
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.
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;
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;
Thank you @Tom This is exactly what I was looking for.
Thanks @error_prone for the right direction
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.
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.