Using infile with tab delimiter and single quotes

[deleted_user]

I am trying to import a tab-delimited file, but each variable also is surrounded by single quotes. I'm not sure how to handle that combination. Thanks for any help.

[sbb]

Using a DATA step, you can input the delimited data-fields as SAS CHARACTER type variables and use a function like COMPRESS to eliminate the quote marks and also use an INPUT function with BEST. to convert the fields to SAS NUMERIC variables.

Scott Barry
SBBWorks, Inc.


90 data _null_;
91 infile cards dlm=',';
92 input (a c_b c_c c_d) ($) ;
93 b = input(compress(c_b,"'"),best.);
94 c = input(compress(c_c,"'"),best.);
95 d = input(compress(c_d,"'"),best.);
96 putlog _all_;
97 cards;

a='a' c_b='1.1' c_c='1.2' c_d='1.3' b=1.1 c=1.2 d=1.3 _ERROR_=0 _N_=1
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
99 run;

[deleted_user]

Sorry, but I can't even read the data in as tab-delimited with the single quotes appearing as part of the variable. The file is tab-delimited, but it doesn't act like it. If I use the single quotes as delimiters and create dummy fields where the tabs are, those fields appear in the data set as \tab - so doesn't that verify that they are tabs?

[sbb]

Suggest you might get more effective feedback if you share some SAS-generated log output with your DATA step code that is not working. As suggested, you will need to input the data values into SAS CHARACTER variables and remove the surrounding quote-marks, then use the INPUT function to convert the CHARACTER variable to a NUMERIC variable.

For your diagnostic logic, suggest you add the following statement and run a few records:

OPTIONS OBS=nnn; /* set an input processing limit */

PUTLOG _ALL_; /* add to DATA step code after all INPUT processing */


Scott Barry
SBBWorks, Inc.

[Cynthia_sas]

Hi:
"\tab" is an RTF control string for indicating a TAB. If you look at your tab-delimited file with NOTEPAD, you should see spaces and not \tab.... like this

My c:\temp\tab_dlm.txt file as it looks in Notepad (those spaces between each field are an expanded "tab" character '09'x -- which I can verify by looking at the input file with a hexadecimal viewer):
[pre]
'aaa' 'something' 'something else'
'bbb' 'morestuff' 'even more'
[/pre]

It is sounding to me as though you need to work with Tech Support on this issue, since I'm not sure you actually have a tab-delimited file. Tech Support is best able to look at the contents of your input file to determine whether you have real tab characters (ASCII '09'x) or RTF control characters (\tab).

To send a question to Tech Support, go to http://support.sas.com/ and in the left-hand navigation pane, click on the link entitled "Submit a Problem". Alternately, you can go directly to the Tech Support Problem Form here:
http://support.sas.com/ctx/supportform/createForm

cynthia

[deleted_user]

Thanks very much for all your help.

I actually got the data to read in by this:

data test;
length var1 $7 var2 $9 var3 $30 var4 $1 var5 $30 var6 $19 var7 $3;
infile hcpf dsd dlmstr="\tab " missover;
input var1 $ var2 $ var3 $ var4 $ var5 $ var6 $ var7 $;
run;

[Cynthia_sas]

Hi:
DSD seems to handle the single quotes for me. I have a tab-delimited file (without column names in row 1) stored in the file c:\temp\tab_dlm.txt. The fields are all surrounded by single quotes. This works for my data file. It should also work if a numeric variable is in quotes because the DSD option strips quotes and your informat would be used to read the field.

The doc on DSD is here:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000146932.htm

Since my system is Windows, I used the ASCII hex value for tab ('09'x) for the DLM= option. On an ASCII platform, the hexadecimal representation of a tab is '09'x. On an EBCDIC platform, the hexadecimal representation of a tab is a '05'x.

There's a Tech Support example here:
http://support.sas.com/kb/24/615.html

cynthia
[pre]
data readtab;
length var1 $3 var2 $18 var3 $18;
infile 'c:\temp\tab_dlm.txt' dsd dlm='09'x;
input var1 $ var2 $ var3 $;
run;

proc print data=readtab;
run;

[/pre]