topic Re: How to extract an external file into a sas dataset in SAS Programming

How to extract an external file into a sas dataset in EG(windows)

annypanny — Wed, 15 Apr 2020 10:45:55 GMT

How can I extract the file “OnlineUsers<date>.txt” in to a sas dataset into the location mentioned below:

For the extraction I have to append all the related file in the input folder.

A copy of the latest file will be moved into an Archive folder. Also in the archive folder any “OnlineUsers<date>.txt” files with age more than 30 days are to be deleted.

Input folder : /sas/sasdata/Lev1/External_Files/

Output : /sas/sasteam/Lev1//Sales_MI

Archive folder: /sas/sasdata/Lev1/External_Files/archive

For now extract the file as this:

Username	Character
Forename	Character
Surname	Character
EmailAddress	Character
LastLoginDate	Character

first few lines of one file in a text editor is,

NULL|Alan|Brown|223440@cofundsmigration.co.uk|NULL

paul@prosperity-ifa.co.uk|Paul|Bonomini|paul@prosperity-ifa.co.uk|Mar 30 2020 9:47AM

colin@prosperity-ifa.co.uk|Colin|Fawcett|colin@prosperity-ifa.co.uk|Apr 3 2020 8:36AM

I have use the below code but nothing working.

</filename users14 "/sas/sasdata/Lev1/External_Files/Cofunds_Files/mi/OnlineUsers20200414.txt";

filename users10 "/sas/sasdata/Lev1/External_Files/Cofunds_Files/mi/OnlineUsers20200410.txt";

/*onlineuser20200414.txt and onlineuser20200410.txt was present in that location after I search for it*/

data user_data14;
infile users14;
input Username $
Forename $
Surname $

Lastlogindate $;

run;

data user_data10;
infile users10;
input Username $
Forename $
Surname $

Lastlogindate $;

run;

proc append base=user_data10 data=user_data14;
run;

filename users pipe "/sas/sasdata/Lev1/External_Files/Cofunds_Files/mi/";
data files;
infile users truncover;
input filename $100.;
date = input(scan(scan(scan(filename,-1,'/'),1,'.'),3,'_'), mmddyy8.);
run;

proc sort data=files;
by descending date;
run;

data _null_;
set files;
call symput('infile',filename);
stop;
run;

data archive;
format date mmddyy8.;
if date=today() then do;
fcopy("/sas/sasdata/Lev1/External_Files/Cofunds_Files/mi/", "/sas/sasdata/Lev1/External_Files/Cofunds_Files/mi/archive");
end;
run;
data delete;
format period yyyymmdd.;
x='20200414';

y=filename(f-name, "physical-name");
if period=intck('month', x, -30, 'sameday') then do;

fdelete(f-name)
end;
run;

The above code is wrong and incomplete it more like a pseudo-code, can anybody help.

EDITED

Re: How to extract an external file into a sas dataset

kelxxx — Wed, 15 Apr 2020 08:23:14 GMT

Hello,

It s on Linux/Unix or Windows ?

Thanks.

Re: How to extract an external file into a sas dataset

Kurt_Bremser — Wed, 15 Apr 2020 08:24:21 GMT

Open your text file with a text editor and copy/paste the first few lines into a window opened with </>.

Re: How to extract an external file into a sas dataset

annypanny — Wed, 15 Apr 2020 08:26:13 GMT

windows

Re: How to extract an external file into a sas dataset

kelxxx — Wed, 15 Apr 2020 08:31:23 GMT

1- in your input folder, you have many txt like "OnlineUsersxxxxxx.txt" (in Windows)

2- you want to import the lasted into a table SAS (in SAS) and create an copy and move to archived folder (in Windows)

3- with the table sas imported , you want to append with a table SAS historique existed (in SAS)

4- you want to delete the file externe aged over 30 day in the archived folder? (in Windows)

It s that you want?

Thanks

Re: How to extract an external file into a sas dataset

annypanny — Wed, 15 Apr 2020 08:37:04 GMT

yes some of the files are with no timestamp also like userxyz.txt too but overall what you written is all I want

Re: How to extract an external file into a sas dataset

annypanny — Wed, 15 Apr 2020 08:39:06 GMT

username forename surname lastlogindate
abc@x ab c 20200412

Re: How to extract an external file into a sas dataset

Kurt_Bremser — Wed, 15 Apr 2020 08:56:47 GMT

So I copied your text into a file stored in my UE shared folder, and ran this code successfully:

data onlineusers20200415;
infile "/folders/myfolders/onlineusers20200415.txt" dlm=' ' firstobs=2 dsd truncover;
input username :$50. forename :$10. surname :$20. lastlogindate :yymmdd8.;
format lastlogindate yymmddd10.;
run;

Re: How to extract an external file into a sas dataset in EG(windows)

kelxxx — Wed, 15 Apr 2020 09:45:35 GMT

/*===============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?

Re: How to extract an external file into a sas dataset

annypanny — Wed, 15 Apr 2020 10:34:12 GMT

and how can I pregress with rest of the question? any idea sir?

Re: How to extract an external file into a sas dataset

annypanny — Wed, 15 Apr 2020 10:48:43 GMT

Re: How to extract an external file into a sas dataset

Kurt_Bremser — Wed, 15 Apr 2020 11:06:42 GMT

Now this looks completely different than what you posted before.

use a TEXT editor (e.g. the Windows Editor; just enter "editor" after clicking the Start button) to open your file; my preferred tool for working with text in Windows is Notepad++
copy/paste the contents into a window opened with </>:

Try this code:

data want;
infile datalines dlm='|' dsd truncover firstobs=2;
input
  Username :$50.
  Forename :$20.
  Surname :$50.
  EmailAddress :$50.
  LastLoginDate :ANYDTDTM20.
;
format lastlogindate e8601dt20.;
datalines;
Username|Forename|Surname|EmailAddress|LastLoginDate|
NULL|Alan|Brown|223440@cofundsmigration.co.uk|NULL
paul@prosperity-ifa.co.uk|Paul|Bonomini|paul@prosperity-ifa.co.uk|Mar 30 2020 9:47AM
colin@prosperity-ifa.co.uk|Colin|Fawcett|colin@prosperity-ifa.co.uk|Apr 3 2020 8:36AM
;

to read your actual file, replace the keyword DATALINES in the INFILE statement with the path to your file (in quotes), and replace the datalines block with a run; statement.

Re: How to extract an external file into a sas dataset

Kurt_Bremser — Wed, 15 Apr 2020 11:09:45 GMT

@annypanny wrote:
and how can I pregress with rest of the question? any idea sir?

We'll cover that once you have managed to read one file successfully.

Re: How to extract an external file into a sas dataset

annypanny — Wed, 15 Apr 2020 13:09:53 GMT

it is working for me now, as I have to extract all as character so I have used
LastLoginDate : $50. and not in datetime, it is mentioned in the question also.
Thank u for this sir, can we move forward?

Re: How to extract an external file into a sas dataset

Kurt_Bremser — Wed, 15 Apr 2020 13:29:41 GMT

Why would you want to store a datetime value as an ugly string that is unusable? You lose all of the functionality that SAS has built for handling date and time values.

So I'll stay with using the datetime string as a datetime value.

So we have code for one instance:

data yourlib.onlineusers200415;
infile "/location/onlineusers200415.txt" dlm='|' dsd truncover firstobs=2;
input
  Username :$50.
  Forename :$20.
  Surname :$50.
  EmailAddress :$50.
  LastLoginDate :ANYDTDTM20.
;
format lastlogindate e8601dt20.;
run;

We want the filename to be variable, and determine the name of the dataset:

%let fname=onlineusers200415;

data yourlib.&fname;
infile "/location/&fname..txt" dlm='|' dsd truncover firstobs=2;
input
  Username :$50.
  Forename :$20.
  Surname :$50.
  EmailAddress :$50.
  LastLoginDate :ANYDTDTM20.
;
format lastlogindate e8601dt20.;
run;

which can quickly be turned into a macro:

%macro import_one(fname);
data yourlib.&fname;
infile "/location/&fname..txt" dlm='|' dsd truncover firstobs=2;
input
  Username :$50.
  Forename :$20.
  Surname :$50.
  EmailAddress :$50.
  LastLoginDate :ANYDTDTM20.
;
format lastlogindate e8601dt20.;
run;
%mend;
%import_one(onlineusers200415)

Now let's feed this macro from a list of files:

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;

Care must be taken that the filenames of the source files (with the exception of the .txt extension) can be used as valid SAS names.

Re: How to extract an external file into a sas dataset in EG(windows)

kelxxx — Wed, 15 Apr 2020 14:31:38 GMT

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

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

/*==== 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;

/*==== 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,".");

/*==== 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;

Re: How to extract an external file into a sas dataset

annypanny — Thu, 16 Apr 2020 06:37:07 GMT

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?

Re: How to extract an external file into a sas dataset

Kurt_Bremser — Thu, 16 Apr 2020 06:46:38 GMT

@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;

Re: How to extract an external file into a sas dataset

annypanny — Thu, 16 Apr 2020 07:31:14 GMT

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

Re: How to extract an external file into a sas dataset

Kurt_Bremser — Thu, 16 Apr 2020 07:35:37 GMT

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