topic Re: How to use an observation as a macro to the file path for importing the latest refreshed file in SAS Programming

How to use an observation as a macro to the file path for importing the latest refreshed file

L10_guri — Tue, 17 May 2022 16:41:35 GMT

Hello to every one!
I have a report in SAS that reads few .txt files that are distinguished from the date of the last refresh i.e

20220516_filenameabc

20220509_filenameabc

20220502_filenameabc

With the scripts below I was able to extract the latest refresh:

data filenames;
length fref $8 fname $200;
did = filename(fref,'path\Automated Extracts');
did = dopen(fref);
do i = 1 to dnum(did);
fname = dread(did,i);
output;
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;


data latest;
set filenames;
length ldate $8 ;
ldate = substr(fname, 1,8);
if substr (ldate ,1,1) ~= 2 then delete ;
drop fname ;
run;


proc sort data = latest;
by descending ldate ; run;
proc sort nodupkey data = latest;
by descending ldate ; run;

data latestd ;
set latest (OBS=1 FirstOBS=1) ; run ;

The part that I want to automate is this:
%LET INDATAFILE7 = 'path\Automated Extracts\20220516_filenameabc.txt';
Instead of manually updating here 20220516 to use a macro that reads this from the dataset created with the above script so I don't need to go every time that I run the report to check the latest file and manually change that part!

Any suggestion what I can do better here?

Thanks for your time and support!

Re: How to use an observation as a macro to the file path for importing the latest refreshed file

PaigeMiller — Tue, 17 May 2022 16:52:34 GMT

You don't need a macro to read all text files in a folder. You can simply use an *.txt in the INFILE statement.

https://blogs.sas.com/content/sasdummy/2018/10/09/read-multiple-text-files/

Re: How to use an observation as a macro to the file path for importing the latest refreshed file

japelin — Tue, 17 May 2022 16:54:20 GMT

data _null_;
  set work.latestd;
  call symputx('INDATAFILE7','path\Automated Extracts\'||ldate||'_filenameabc.txt');
run;

Re: How to use an observation as a macro to the file path for importing the latest refreshed file

L10_guri — Tue, 17 May 2022 17:28:16 GMT

The folder that has the files has more files that I'm looking to import in SAS. I need to get the most refreshed one.
Currently the folder has more than 350 files with different dates when the file was refreshed. The unique ones are only 5. That is the reason that I'm trying to get these one only!
Thanks for checking!

Re: How to use an observation as a macro to the file path for importing the latest refreshed file

s_lassen — Tue, 17 May 2022 17:35:11 GMT

If your files are named like <YYYYMMDD>_<something else>, this may work:

data _null_;
  did = filename(fref,'path\Automated Extracts');
  did = dopen(fref);
  do i = 1 to dnum(did);
     currentfile=dread(did,i);
     if substr(currentfile,1,1)='2' and fname<currentfile then fname=currentfile;
    end;
  call symputx('INDATAFILE7','path\Automated Extracts\'||fname);
  did = dclose(did);
  did = filename(fref);
run;

Which should give you the filename beginning with "2" having the highest (latest) filename in your macro variable INDATAFILE7.

Re: How to use an observation as a macro to the file path for importing the latest refreshed file

L10_guri — Tue, 17 May 2022 18:08:01 GMT

It gave me the below error:

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
161:18 162:15 168:18
NOTE: Variable fref is uninitialized.
NOTE: Argument 1 to function DNUM(0) at line 163 column 15 is invalid.
ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY
expression is missing, zero, or invalid.
did=0 fref=. i=1 currentfile= fname= _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the
operations have been set to missing values.
Each place is given by: (Number of times) at (Line):(Column).
1 at 163:15
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds

Re: How to use an observation as a macro to the file path for importing the latest refreshed file

Tom — Tue, 17 May 2022 18:31:15 GMT

Just put the name of the most recent file into a macro variable.

proc sql noprint;
select fname into :fname trimmed
   from filenames 
   order by fname desc
   where fname like '202%'
;
quit;

Once you have the macro variable use it in your code that reads the file.

Make sure to use double quotes around the string so that the macro variable value is resolved.

data want;
  infile "path/Automated Extracts/&fname" .... ;
  ...
run;

Re: How to use an observation as a macro to the file path for importing the latest refreshed file

andreas_lds — Wed, 18 May 2022 10:18:55 GMT

Please use the search function, your issue has been asked and answers multiple times, so you will find something useful.