topic Re: importing tab delimited files in SAS Enterprise Guide

importing tab delimited files

woo — Wed, 30 Sep 2015 23:50:57 GMT

hi friends please help,

have one text file like this (test.text); (its tab delimited) (showing separate variables by bold and not bold) - trying to import it but not getting desire result, specially want to separate out red color and blue color as separate variable

have

zone mkt /sas/log1/02_mkt/logs 01-01_this_is_the_xyz_mkt_2015.09.30_04.01.37.log (ok) 0.104072 Sat Sep 19 04:04:51 2015

zone fin /sas/log1/02_fin/logs 05-05_this_is_the_xyz_fin_2015.09.30_04.05.00.log (ok) 0.104072 Sat Sep 19 04:21:51 2015

zone sales /sas/log1/02_sales/logs 10-13_this_is_the_xyz_sales_2015.09.30_04.22.13.log (ok) 0.104072 Sat Sep 19 04:54:09 2015

want (example for dataline 1 and it should be same for others too)

var1=zone

var2=mkt

var3=/sas/log1/02_mkt/logs

var4=01-01_this_is_the_xyz_mkt

date=2015.09.30

start=04.01.37

var7=(ok)

var8=0.104072

var9=Sat

var10=Sep

var11=19

end=04:04:51

var13=2015

Re: importing tab delimited files

Tom — Thu, 01 Oct 2015 00:20:14 GMT

Your example does NOT look like it it tab delimited. it looks more like the values are space delimited.

But outside of the delimiters (if any) in the file it looks like you are trying to parse the filename into pieces.

path= '/sas/log1/02_mkt/logs';

filename='01-01_this_is_the_xyz_mkt_2015.09.30_04.01.37.log' ;

datestr=scan(filename,-2,'_');

timestr=scan(filename,-2,'._');

Re: importing tab delimited files

woo — Thu, 01 Oct 2015 21:58:28 GMT

PROC IMPORT is not getting full "filename" is there a way to define length in PROC IMPORT,

Re: importing tab delimited files

woo — Thu, 01 Oct 2015 22:12:57 GMT

never mind, used filename and data step and it worked,,,

Re: importing tab delimited files

woo — Thu, 01 Oct 2015 22:57:33 GMT

but this statement: timestr=scan(filename,-2,'._');

for this value: filename='01-01_this_is_the_xyz_mkt_2015.09.30_04.01.37.log' ;

give this output: 37

Re: importing tab delimited files

Tom — Fri, 02 Oct 2015 01:41:55 GMT

Depending on how consistent the data is you could either parse it into more pieces and build it back up.

Or it might be easier to parse it just on the underscore and then use substr to remove the '.log' from the end.

timestr=substr(scan(filename,-1,'_'),1,10);

Our you could look at using regular expressions.

Re: importing tab delimited files

woo — Fri, 02 Oct 2015 20:34:38 GMT

so for example how can we get this done if we want to scan string from left to right?

have

01-01_this_is_the_xyz_mkt_2015.09.30_04.01.37.log

01-01_this_is_the_xyz_market_fin_sale_2015.09.30_04.01.37.log

this_is_the_xyz_fin_2015.09.30_04.05.00.log

want

01-01_this_is_the_xyz_mkt_

01-01_this_is_the_xyz_market_fin_sale_

this_is_the_xyz_fin_

Re: importing tab delimited files

ballardw — Fri, 02 Oct 2015 22:03:32 GMT

If those dates always start with 2015, or even 20 ( or 20 and 19) you could use index function to find that location and then substr from 1 to that position -1 (the space before 2015).

This will be subtle Y2K type potential error but would likely not kick in until Jan 2100...