BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Elenatec
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

11 REPLIES 11
Ksharp
Super User

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

Elenatec
Calcite | Level 5

Thanks for your quick reply, I am still working on your code to make it compatible to my requirement

Many thanks,

Elenatec
Calcite | Level 5

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.

Elenatec
Calcite | Level 5

OK Sorry I found the issue, I need to put those code before "output;" :smileysilly:

anyway thanks

Elenatec
Calcite | Level 5


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            

allurai0412
Fluorite | Level 6

hi ,

try this ,  for .txt files..

But Ksharp's solution will be good ...i guess DATE ..aslo appears..Smiley Happy


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

Elenatec
Calcite | Level 5


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.

Aad
Calcite | Level 5 Aad
Calcite | Level 5

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 ...Smiley Sad

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;

Ksharp
Super User

If there are a couple of dates needed to import in a single file. Your code will not work.

Aad
Calcite | Level 5 Aad
Calcite | Level 5

U're right; the initial problem description however didn't mention any "multiple date report", hence my - often overlooked - suggestion to use INPUT @ 'textstring'.

Elenatec
Calcite | Level 5

Thanks for both of your guys, but this forum only able me to select one correct answers Smiley Sad I wish I could put more correct answers for you guys. I still research both codes XD

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1215 views
  • 8 likes
  • 4 in conversation