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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.