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

## Log file into SAS Dataset

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).

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Log file into SAS Dataset

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.

7 REPLIES 7
Obsidian | Level 7

## Re: Log file into SAS Dataset

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

Quartz | Level 8

## Re: Log file into SAS Dataset

Hi,

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

Thank you

Super User

## Re: Log file into SAS Dataset

Log files are text. Text manipulation is done with proper tools; SAS and Excel are not proper tools for text.

Super User

## Re: Log file into SAS Dataset

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.

Quartz | Level 8

## Re: Log file into SAS Dataset

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.

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.

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.

Thank you!

Opal | Level 21

## Re: Log file into SAS Dataset

@jei

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

``````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;
``````

Super User

## Re: Log file into SAS Dataset

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.

Discussion stats
• 7 replies
• 3729 views
• 1 like
• 5 in conversation