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

[annypanny]

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,

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

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

[kelxxx]

Hello,

It s on Linux/Unix or Windows ?

Thanks.

[annypanny]

windows

[kelxxx]

So

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

[annypanny]

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

[Kurt_Bremser]

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

[annypanny]

username forename surname lastlogindate
abc@x ab c 20200412

[Kurt_Bremser]

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;

[annypanny]

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

[Kurt_Bremser]

---

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

[annypanny]

sir here is the first few lines,
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

in the reply I did'nt checked the editor as it was unavailable, and can you modify your code?

[Kurt_Bremser]

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.

[annypanny]

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?

[Kurt_Bremser]

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.

[annypanny]

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?