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
Hello,
It s on Linux/Unix or Windows ?
Thanks.
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
Open your text file with a text editor and copy/paste the first few lines into a window opened with </>.
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 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.
Now this looks completely different than what you posted before.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.