Desktop productivity for business analysts and programmers

importing tab delimited files

Reply
Super Contributor
Super Contributor
Posts: 318

importing tab delimited files

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 

             

       

 

 

 

 

Super User
Super User
Posts: 6,323

Re: importing tab delimited files

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,'._');

 

 

Super Contributor
Super Contributor
Posts: 318

Re: importing tab delimited files

[ Edited ]

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

 

 

Super Contributor
Super Contributor
Posts: 318

Re: importing tab delimited files

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

Super Contributor
Super Contributor
Posts: 318

Re: importing tab delimited files

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

 

 

Super User
Super User
Posts: 6,323

Re: importing tab delimited files

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.

Super Contributor
Super Contributor
Posts: 318

Re: importing tab delimited files

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_

Grand Advisor
Posts: 10,210

Re: importing tab delimited files

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

Ask a Question
Discussion stats
  • 7 replies
  • 378 views
  • 0 likes
  • 3 in conversation