topic Re: Collecting multiple record in to one observation in SAS Procedures

Collecting multiple record in to one observation

Elenatec — Fri, 17 May 2013 05:35:03 GMT

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

Re: Collecting multiple record in to one observation

Ksharp — Fri, 17 May 2013 07:47:53 GMT

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

Re: Collecting multiple record in to one observation

allurai0412 — Fri, 17 May 2013 07:56:28 GMT

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

Re: Collecting multiple record in to one observation

Elenatec — Fri, 17 May 2013 08:06:27 GMT

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

Many thanks,

Re: Collecting multiple record in to one observation

Aad — Fri, 17 May 2013 08:09:48 GMT

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;

Re: Collecting multiple record in to one observation

Elenatec — Fri, 17 May 2013 08:31:46 GMT

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.

Re: Collecting multiple record in to one observation

Ksharp — Fri, 17 May 2013 08:35:53 GMT

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

Re: Collecting multiple record in to one observation

Elenatec — Fri, 17 May 2013 08:47:20 GMT

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

Re: Collecting multiple record in to one observation

Aad — Fri, 17 May 2013 08:55:41 GMT

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

Re: Collecting multiple record in to one observation

Elenatec — Mon, 20 May 2013 05:28:51 GMT

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;

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.

Re: Collecting multiple record in to one observation

Elenatec — Mon, 20 May 2013 06:51:57 GMT

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

anyway thanks

Re: Collecting multiple record in to one observation

Elenatec — Mon, 20 May 2013 09:06:26 GMT

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