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

Hi,

 

I need to convert the attached log file into dataset. The log file contains multiple delimiters such as "|" , tab, and a special character (please refer to the attached log file).

 

Thanks for your help in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Your infile actually has three delimiters:

- the pipe character

- hex 08 to separate items in the long string

- hex 09 to separate events and values within an item

 

So you need a do loop for the items and two different splits along these separators:

data jei;
infile '$HOME/sascommunity/jei.txt' dlmstr=CRLF dlm='|' truncover;
input
  status :$5.
  TIMESTAMP :$20.
  variablestr :$500.
  n :$1.
;
length
  event $50
  val 8
;
do i = 1 to countw(variablestr,'08'x);
  event = scan(scan(variablestr,i,'08'x),1,'09'x);
  val = input(scan(scan(variablestr,i,'08'x),2,'09'x),best.);
  output;
end;
drop i variablestr;
run;

proc print data=jei noobs;
run;

Partial result:

status      TIMESTAMP       n    event                              val

SV        20170629002459    N    NumberOfUnitsBonded               6729
SV        20170629002459    N    Previous Number of Unit Bonded    6732
SV        20170629002459    N    Number of Unit Bonded             6733
SV        20170629002459    N    UPS                               3433
SV        20170629002459    N    UOL                               6733
SV        20170629002501    N    NumberOfUnitsBonded               6730
SV        20170629002501    N    Previous Number of Unit Bonded    6733
SV        20170629002501    N    Number of Unit Bonded             6734
SV        20170629002501    N    UPS                               3434
SV        20170629002501    N    UOL                               6734
STATE     20170629002501    N    Alarm count per shift                0
STATE     20170629002501    N    Runtime per shift                 8668
STATE     20170629002501    N    MTBA per shift                    8668
SV        20170629002502    N    NumberOfUnitsBonded               6731
SV        20170629002503    N    Previous Number of Unit Bonded    6734
SV        20170629002503    N    Number of Unit Bonded             6735
SV        20170629002503    N    UPS                               3435
SV        20170629002503    N    UOL                               6735
SV        20170629002504    N    NumberOfUnitsBonded               6732
SV        20170629002504    N    Previous Number of Unit Bonded    6735
SV        20170629002504    N    Number of Unit Bonded             6736
SV        20170629002504    N    UPS                               3436
SV        20170629002504    N    UOL                               6736
STATE     20170629002505    N    Alarm count per shift                0
STATE     20170629002505    N    Runtime per shift                 8672
STATE     20170629002505    N    MTBA per shift                    8672
SV        20170629002506    N    NumberOfUnitsBonded               6733
SV        20170629002506    N    Previous Number of Unit Bonded    6736
SV        20170629002506    N    Number of Unit Bonded             6737
SV        20170629002506    N    UPS                               3437
SV        20170629002506    N    UOL                               6737
SV        20170629002508    N    NumberOfUnitsBonded               6734
SV        20170629002508    N    Previous Number of Unit Bonded    6737
SV        20170629002508    N    Number of Unit Bonded             6738
SV        20170629002508    N    UPS                               3438
SV        20170629002508    N    UOL                               6738

To make the data more analyzable for SAS, consider to convert the timestamp value to a SAS datetime value.

View solution in original post

7 REPLIES 7
Clark
Obsidian | Level 7

Would you be able to outline your target position for the dataset, as that would help someone being able to give you some assistance.

jei
Quartz | Level 8 jei
Quartz | Level 8

Hi,

I wish to have the same format of the output on the attached file.

 

Thank you

Tom
Super User Tom
Super User

So what did you try?

Looking at the file you posted in the viewer on this site it looks like text file that delimited with a |.

It is trivial to read a file that is delimited with | in SAS.

data want ;
  infile 'myfile.txt' dsd dlm='|' truncover ;
  length var1-var5 $200 ;
  input var1-var5;
run;

It does appear that one of column appears to contain a list of values delimited by something that looks like a square in the view, but even that should not be hard to deal with once you have the data in a dataset instead of a text file.

 

jei
Quartz | Level 8 jei
Quartz | Level 8

Hi @Tom

 

I tried the code below

 

DATA TEST_LOG;
    LENGTH
        STATUS           $ 13
        TIMESTAMP        8
        EVENT            $ 2577
        VAL		 8
       	;
    FORMAT
        STATUS           $CHAR13.
        TIMESTAMP        BEST14.
        EVENT            $CHAR2577.
        ;
    INFORMAT
        STATUS           $CHAR13.
        TIMESTAMP        BEST14.
        EVENT            $CHAR2577.
        ;
    INFILE 'E:\TEST.log'
	LRECL=32767
        FIRSTOBS=2
        DLMSTR='7C'x
        TRUNCOVER
		
		;
    INPUT
        STATUS           : $CHAR13.
        TIMESTAMP        : ?? BEST14.
        EVENT            : $CHAR2577.
	;
	ARRAY AR[1] EVENT;
		DO i=1 ;
		AR(i)=scan(EVENT, i, '09'X);
		END;


RUN;

and gives the output below.

 

Screen Shot 2017-10-27 at 3.44.21 PM.png

 

If you can notice, the value whose delimiter is a tab was gone. 

 

I tried your given code and it gives me the output below.

 

Screen Shot 2017-10-27 at 3.48.02 PM.png

 

I want to have another column for the numeric values in var3 column - which are in the most right. In my code, these values did not appear.

 

So basically, I'm wishing to have like the output below.

 

Screen Shot 2017-10-26 at 5.51.24 PM.png

 

Thank you!

 

Patrick
Opal | Level 21

@jei

Just use DLM instead of DLMSTR and add all THREE delimiters as value (a pipe, backspace, tab).

Capture.JPG

DATA TEST_LOG;
    LENGTH
        STATUS           $ 13
        TIMESTAMP        8
        EVENT            $ 100
        VAL		 8
       	;
    FORMAT
        STATUS           $CHAR13.
        TIMESTAMP        BEST14.
        EVENT            $100.
        ;
    INFORMAT
        STATUS           $CHAR13.
        TIMESTAMP        BEST14.
        EVENT            $100.
        ;
    INFILE 'c:\temp\TEST.txt'
	      LRECL=32767
        FIRSTOBS=1
        DLM='7c0809'x
        dsd
        TRUNCOVER
		
		;
    INPUT
        STATUS           : $CHAR13.
        TIMESTAMP        : ?? BEST14.
        EVENT            : $100.
        val              : best15.
	;

RUN;
  

 

Kurt_Bremser
Super User

Your infile actually has three delimiters:

- the pipe character

- hex 08 to separate items in the long string

- hex 09 to separate events and values within an item

 

So you need a do loop for the items and two different splits along these separators:

data jei;
infile '$HOME/sascommunity/jei.txt' dlmstr=CRLF dlm='|' truncover;
input
  status :$5.
  TIMESTAMP :$20.
  variablestr :$500.
  n :$1.
;
length
  event $50
  val 8
;
do i = 1 to countw(variablestr,'08'x);
  event = scan(scan(variablestr,i,'08'x),1,'09'x);
  val = input(scan(scan(variablestr,i,'08'x),2,'09'x),best.);
  output;
end;
drop i variablestr;
run;

proc print data=jei noobs;
run;

Partial result:

status      TIMESTAMP       n    event                              val

SV        20170629002459    N    NumberOfUnitsBonded               6729
SV        20170629002459    N    Previous Number of Unit Bonded    6732
SV        20170629002459    N    Number of Unit Bonded             6733
SV        20170629002459    N    UPS                               3433
SV        20170629002459    N    UOL                               6733
SV        20170629002501    N    NumberOfUnitsBonded               6730
SV        20170629002501    N    Previous Number of Unit Bonded    6733
SV        20170629002501    N    Number of Unit Bonded             6734
SV        20170629002501    N    UPS                               3434
SV        20170629002501    N    UOL                               6734
STATE     20170629002501    N    Alarm count per shift                0
STATE     20170629002501    N    Runtime per shift                 8668
STATE     20170629002501    N    MTBA per shift                    8668
SV        20170629002502    N    NumberOfUnitsBonded               6731
SV        20170629002503    N    Previous Number of Unit Bonded    6734
SV        20170629002503    N    Number of Unit Bonded             6735
SV        20170629002503    N    UPS                               3435
SV        20170629002503    N    UOL                               6735
SV        20170629002504    N    NumberOfUnitsBonded               6732
SV        20170629002504    N    Previous Number of Unit Bonded    6735
SV        20170629002504    N    Number of Unit Bonded             6736
SV        20170629002504    N    UPS                               3436
SV        20170629002504    N    UOL                               6736
STATE     20170629002505    N    Alarm count per shift                0
STATE     20170629002505    N    Runtime per shift                 8672
STATE     20170629002505    N    MTBA per shift                    8672
SV        20170629002506    N    NumberOfUnitsBonded               6733
SV        20170629002506    N    Previous Number of Unit Bonded    6736
SV        20170629002506    N    Number of Unit Bonded             6737
SV        20170629002506    N    UPS                               3437
SV        20170629002506    N    UOL                               6737
SV        20170629002508    N    NumberOfUnitsBonded               6734
SV        20170629002508    N    Previous Number of Unit Bonded    6737
SV        20170629002508    N    Number of Unit Bonded             6738
SV        20170629002508    N    UPS                               3438
SV        20170629002508    N    UOL                               6738

To make the data more analyzable for SAS, consider to convert the timestamp value to a SAS datetime value.

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!

How to Concatenate Values

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.

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
  • 7 replies
  • 3410 views
  • 1 like
  • 5 in conversation