How to extract an external file into a sas dataset

[Kurt_Bremser]

---

@annypanny wrote:
yes all are correct and working for me but what if the filename changes dynamically, suppose yesterday date 15th april 2020 and the filename was OnlineUser20200415 now today I can't extract that file because a new file taken place instead of that i.e., OnlineUser20200416(today's date) and I want to solve it programmatically and not by changing the file name, How can I get that solved?

---

This code from my last post

data files;
length location $8;
rc = filename(location,"/location"); /* insert your path here */
did = dopen(location);
do i = 1 to dnum(did);
  name = dread(did,i);
  if substr(name,1,11) = "OnlineUsers" and scan(name,2,'.') = 'txt'
  /* adapt above condition as needed */
  then output;
end;
rc = dclose(did);
rc = filename(location);
keep name;
run;

data _null_;
set files;
call execute(cats('%nrstr(%import_one(',scan(name,1,'.'),'))'));
/* the scan() function removes the extension */
run;

will read ALL files where the filename satisfies the condition in the data step that reads the directory. You n make your selection by changing the condition.

Now, if you want to select the last in a series, you just sort properly and fetch the last entry:

proc sort data=files;
by name;
run;
/* usually, this is not needed, as directories have their entries sorted anyway, but ... */

data _null_;
set files nobs=nobs point=nobs;
call execute(cats('%nrstr(%import_one(',scan(name,1,'.'),'))'));
stop;
run;

[annypanny]

the above code is giving an error message
1 + %import_one()
_
180
WARNING: Apparent invocation of macro IMPORT_ONE not resolved.
ERROR 180-322: Statement is not valid or it is used out of proper order.

I just want to find out today's file and read that file into a dataset programically. My question is that

[Kurt_Bremser]

You must run the macro definition before you can use the macro.

[annypanny]

I am using this code
data _null_;
length dsnname $100;
today=put(today(),yymmdd7.);
dsnname = compress('c:\temp\'||today||'.txt');
file phold filevar=dsnname notitles;
put today;
run;

but it is giving an error message:
ERROR: Insufficient authorization to access /sas/config/Lev1/SASApp/c:\temp\200416.txt.

[Kurt_Bremser]

You must always keep in mind that you work in a client/server environment. Code you run on the server (via SAS Studio or Enterprise Guide) has to use path names available on the server, and the syntax of the server's operating system.

Since your SAS server is obviously a UNIX system, you need to use the UNIX File System language. And point your file references to locations where you have the required permissions.

[annypanny]

could you please give the soluion that what change I have to do in my code and where, as I am a bit confuse here

[Kurt_Bremser]

I cannot look into your server (if I could, I would either have the police knocking soon, or your IT people would all be fired), so you need to know where to put data in your system environment. Get help from your server administrators, they can tell you where to put data on the server (a good bet is always $HOME), and how to move the data from there to its final intended location.

[kelxxx]

/*===============Preparation================*/
%let input_folder=H:;
%let archi_folder=H:\archive;
%let output_folder=H:\output;
%let name_table_history=lib.xxx;

/*====delete file in archive folder ====*/
/*list of file in folder archive folder*/
Filename filesarc PIPE "dir /b &archi_folder";

data list_file_A_folder;
infile filesarc;
input namefile :$ 50.;
run;

/*delete*/
options noxwait;
data _null_;
set list_file_A_folder(where=(lowcase(namefile) like "onlineusers%.txt"));
_over30=put(date()-30,yymmddN.); /*date over 30*/
_dte=substr(namefile,12,8);/*date in filename*/
if _dte le _over30 then 
CALL SYSTEM(CAT("del /f /q"," ","&archi_folder.\",namefile));
run;

/*=======extract the name of lasted file onlineusers .txt end copy to archive folder======*/
/*list of file in folder input folder*/
Filename filesin PIPE "dir /b &input_folder";

data list_file_I_folder;
infile filesin;
input namefile :$ 50.;
run;

/*last file*/
data _null_;
set list_file_I_folder(where=(lowcase(namefile) like "onlineusers%.txt")) end=eof;
if eof then do;
call symput("lasted_file",cats(namefile));
call SYSTEM(cat("copy /y &input_folder.\",namefile," &archi_folder"));
end;
run;

%put &lasted_file;

%let nametab=%SCAN(&lasted_file,1,".");
%put &nametab;

Filename lastfile "&input_folder\&lasted_file";

/*==== import to table SAS ======*/
data &nametab;
infile lastfile dsd missover <other options ….>;
input 	Username $			/*add the informat in your data source*/
		Forename $
		Surname $
		Lastlogindate $;
run;

/*====append with the table SAS of history====*/
PROC APPEND data=&nametab base=&name_table_history ; QUIT;

Can you test it?

[kelxxx]

It s bizzare, i think that i posted my codes,

1.

/*==== Preparation ===*/
%let input_folder=H:;
%let archive_folder=H:\Archive;
%let tab_history=libname.tabname;

2.

/*==== Delete files aged over 30 days in Archive ===*/
filename filesA PIPE "dir /B &archive_folder";

data file_in_A;
infile filesA;
input filename :$50.;
run;

option noxwait;
data _null_;
set file_in_A(where=(lowcase(filename) like "onlineusers%.txt"));
over30=put(date()-30,yymmddn.);
datefile=substr(filename,12,8);
if datefile le over30 then do;
call system(cat("del /f /q ","&archive_folder.\",filename));
end; 
run;

3.

/*==== Copy lasted file ====*/
filename filesI PIPE "dir /B &input_folder";

data file_in_I;
infile filesI;
input filename :$50.;
run;

data _null_;
set file_in_I(where=(lowcase(filename) like "onlineusers%.txt")) end=eof;
if eof then do;
call symputx("last_file",filename);
call system(cat("copy ","&input_folder.\",filename," &archive_folder"));
end;
run;
%let nomtab=%SCAN(&last_file,1,".");

4.

/*==== import to table SAS====*/
data &nomtab;
infile "&input_folder.\&last_file." ......;
input .....;
run;

/*==== append with table history ====*/
proc append data=&nomtab base=&tab_history; quit;