Help using Base SAS procedures

Collecting multiple record in to one observation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Collecting multiple record in to one observation

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


Accepted Solutions
Solution
‎05-17-2013 03:47 AM
Super User
Posts: 10,020

Re: Collecting multiple record in to one observation

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


All Replies
Solution
‎05-17-2013 03:47 AM
Super User
Posts: 10,020

Re: Collecting multiple record in to one observation

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

Occasional Contributor
Posts: 17

Re: Collecting multiple record in to one observation

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

Many thanks,

Occasional Contributor
Posts: 17

Re: Collecting multiple record in to one observation

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.

Occasional Contributor
Posts: 17

Re: Collecting multiple record in to one observation

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

anyway thanks

Occasional Contributor
Posts: 17

Re: Collecting multiple record in to one observation


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            

Frequent Contributor
Posts: 97

Re: Collecting multiple record in to one observation

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

Occasional Contributor
Posts: 17

Re: Collecting multiple record in to one observation

Posted in reply to allurai0412


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.

Occasional Contributor Aad
Occasional Contributor
Posts: 8

Re: Collecting multiple record in to one observation

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;

Super User
Posts: 10,020

Re: Collecting multiple record in to one observation

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

Occasional Contributor Aad
Occasional Contributor
Posts: 8

Re: Collecting multiple record in to one observation

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

Occasional Contributor
Posts: 17

Re: Collecting multiple record in to one observation

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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