Hi,
I was trying to collect data from a txt file I having issue collecting information with DATE from the top of each data.
GTE SKYLINE TEAM PAGE 1
TRANSACTION FILE LISTING FOR 08-05-12
OPTIONS: -W (C=TU)
TIME TRM USER CMD DETAILS
07:31 SMB37 59872346 TU TABLE METER INVENTORY PATRON
SMB37 3100.00 164000.00 0
07:32 MB635 69347765 TU TABLE METER INVENTORY PATRON
MB635 20100.00 650000.00 5
07:32 PTP66 86923644 TU TABLE METER INVENTORY PATRON
PTP66 3475.00 116000.00 2
07:32 SMB12 6234212 TU TABLE METER INVENTORY PATRON
SMB12 26700.00 218000.00 8
so as you can see the data have to collect 2 rows and the date from the top of each data record
I was trying to retain the date and use 2 input statement in to 1 observation. but the Date still getting the string inventory or data.
so I hope the result look like this
Date TIME TRM USER CMD TABLE METER INVENTORY PATRON
08-05-12 07:31 SMB37 59872346 TU SMB37 3100.00 164000.00 0
08-05-12 07:32 MB635 69347765 TU MB635 20100.00 650000.00 5
08-05-12 07:32 PTP66 86923644 TU PTP66 3475.00 116000.00 2
OK.
data have; infile cards truncover; input @ ; id1=prxparse('/(\d\d-\d\d-\d\d)/o'); id2=prxparse('/(\d\d:\d\d)/o'); length date $ 10; retain date; if prxmatch(id1,_infile_) then date=prxposn(id1,1,_infile_); else if prxmatch(id2,_infile_) then do; input (TIME TRM USER CMD ) (: $40.) / (TABLE METER INVENTORY PATRON) (: $40.); output; end; drop id1 id2 ; cards; GTE SKYLINE TEAM PAGE 1 TRANSACTION FILE LISTING FOR 08-05-12 OPTIONS: -W (C=TU) TIME TRM USER CMD DETAILS 07:31 SMB37 59872346 TU TABLE METER INVENTORY PATRON SMB37 3100.00 164000.00 0 07:32 MB635 69347765 TU TABLE METER INVENTORY PATRON MB635 20100.00 650000.00 5 07:32 PTP66 86923644 TU TABLE METER INVENTORY PATRON PTP66 3475.00 116000.00 2 07:32 SMB12 6234212 TU TABLE METER INVENTORY PATRON SMB12 26700.00 218000.00 8 ; run;
Ksharp
Message was edited by: xia keshan
OK.
data have; infile cards truncover; input @ ; id1=prxparse('/(\d\d-\d\d-\d\d)/o'); id2=prxparse('/(\d\d:\d\d)/o'); length date $ 10; retain date; if prxmatch(id1,_infile_) then date=prxposn(id1,1,_infile_); else if prxmatch(id2,_infile_) then do; input (TIME TRM USER CMD ) (: $40.) / (TABLE METER INVENTORY PATRON) (: $40.); output; end; drop id1 id2 ; cards; GTE SKYLINE TEAM PAGE 1 TRANSACTION FILE LISTING FOR 08-05-12 OPTIONS: -W (C=TU) TIME TRM USER CMD DETAILS 07:31 SMB37 59872346 TU TABLE METER INVENTORY PATRON SMB37 3100.00 164000.00 0 07:32 MB635 69347765 TU TABLE METER INVENTORY PATRON MB635 20100.00 650000.00 5 07:32 PTP66 86923644 TU TABLE METER INVENTORY PATRON PTP66 3475.00 116000.00 2 07:32 SMB12 6234212 TU TABLE METER INVENTORY PATRON SMB12 26700.00 218000.00 8 ; run;
Ksharp
Message was edited by: xia keshan
Thanks for your quick reply, I am still working on your code to make it compatible to my requirement
Many thanks,
Hi Ksharp,
after I generate the datasets I try to convert the "Time" to time format because I want to compare the time segment to another new column, but I having issue converting the "Input time" to "time5." format here is my code
the same code at top
attrib Time_segment length=5 format=5.;
attrib tSegment format=2.;
Time_segment = input(substr(Time,1,2)||':'||substr(Time,4), time5.);
tSegment=hour(timepart(Time_Segment))+1;
or
attrib Time_segment format=2.;
Time_segment = substr(Time, 1, 2) + 1;
both return "." or missing, so anyway to solve this issue? I am not sure about those format are correct.
OK Sorry I found the issue, I need to put those code before "output;" :smileysilly:
anyway thanks
Hi Ksharp,
what if I have additinal data extra which look like this in some of the row will this capture the dataset?
TIME TRM USER CMD DETAILS
07:31 SMB37 59872346 TU TABLE METER INVENTORY PATRON
SMB37 3100.00 164000.00 0
SMB39 3100.00 164000.00 0
hi ,
try this , for .txt files..
But Ksharp's solution will be good ...i guess DATE ..aslo appears..
data Inventory;
FORMAT TIME TIME5.;
infile 'C:\Users\galax_allu\Desktop\inveNtory2.txt' pad ;
input @ ;
if left(_infile_) in :('PAGE','GTE','OPTIONS','TRANSACTION','TIME') THEN DELETE;
if not missing(_infile_)and length(trimn(_infile_))>0 then
input #1 time time5. trm $ user $ cmd $
#2 @29 TABLE $ METER INVENTORY PATRON ;
else delete;
run;
Regards
Allu
thanks Allu your answers unfortunely the if then delete section you have take away the 'TRANSACTION' because the date was at the same line, and I need to retain it.
Hi, this program will do the job for you. Apart from the declarations, only the last statement is executed for each input line, and that single statement will do the job for jou. No PRXPARSEs, no checks for missing or empty lines or whatsoever.
The power of the 'INPUT @ 'text string' is terribly powerful yet almost unknown ...
Eventually, following changes may be required:
-1- It your date is not in the DD-MM-YY format, replace the first ATTRIB line accordingly
-2- Fill the proper reference to your input in the INFILE statement below
DATA AnyName;
ATTRIB Date Format = ddmmyyd8. InFormat = ddmmyy.
Time Format = time5. InFormat = time.
Trm Format = $8.
User Format = 8.
Cmd Format = $2.
Table Format = $5.
Meter Format = 12.2
Inventory Format = 12.2
Patron Format = 4.;
RETAIN Date;
INFILE TextFile;
IF _N_=1 THEN INPUT @ 'TRANSACTION FILE LISTING FOR' Date
@ 'DETAILS';
INPUT Time Trm User Cmd
/ Table Meter Inventory Patron;
RUN;
If there are a couple of dates needed to import in a single file. Your code will not work.
U're right; the initial problem description however didn't mention any "multiple date report", hence my - often overlooked - suggestion to use INPUT @ 'textstring'.
Thanks for both of your guys, but this forum only able me to select one correct answers I wish I could put more correct answers for you guys. I still research both codes XD
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.