Hi have a daily report that imports yesterday sale the file name is Sales for 27MAY2020.csv. manually I can import without issue my question is how to auto import file ? in sas I have
options symbolgen;
%let StartDate = %sysfunc(intnx(day.1, "&SYSDATE"D,-1), date9.); /* FormaV: 14DEC2009 StartDate is on a Monday*/
%let Space1 = _d;
%let StartYear = %eval(%SUBSTR(&StartDate,8,2));
%let StartMonthName = %SUBSTR(&StartDate,3,3);
%let StartDay= %eval(%SUBSTR(&StartDate,1,2));
%let FileExt = .xls;
%let FileExt2 = .xlsb;
%let sMonth = 01;
%Macro SMonthNum(StartMonthNameRef = );
%if &StartMonthNameRef = JAN %then %let sMonth = 01;
%else %if &StartMonthNameRef = FEB %then %let sMonth = 02;
%else %if &StartMonthNameRef = MAR %then %let sMonth = 03;
%else %if &StartMonthNameRef = APR %then %let sMonth = 04;
%else %if &StartMonthNameRef = MAY %then %let sMonth = 05;
%else %if &StartMonthNameRef = JUN %then %let sMonth = 06;
%else %if &StartMonthNameRef = JUL %then %let sMonth = 07;
%else %if &StartMonthNameRef = AUG %then %let sMonth = 08;
%else %if &StartMonthNameRef = SEP %then %let sMonth = 09;
%else %if &StartMonthNameRef = OCT %then %let sMonth = 10;
%else %if &StartMonthNameRef = NOV %then %let sMonth = 11;
%else %if &StartMonthNameRef = DEC %then %let sMonth = 12;
%Mend;
%SMonthNum(StartMonthNameRef = &StartMonthName);
Settlements for &startdate&FILEEXT"
Solved! Go to Solution.
If the files are always in the same place, then you could use a combination of the Directory / Sample / Dynamic Input tools.
First, point the Directory tool where the files are located. Then sort by Creation Time. Use the Sample Tool to pick the first '1'. Now you have the most recent file in that directory and its path. Then you can use that path in the Dynamic Input to open that file.
How about pointing a directory tool to the folder with setting *.csv, then a sort tool to sort on creation date, descending, sample tool to keep the 1st one and pass that to a dynamic input tool.
I rec this message Dynamic Input In-DB (20) Unable to find connection "p:\_report \ sales for 28may2020.csv"
in the dynamic in db I selected full path
Is this what you're trying to do?
Or is it this?
I was assuming it's the 2nd one, where you just bring data from a file into a standard data stream, but defining the source path and filename from a field in your incoming data stream.