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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.