BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
6 REPLIES 6
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
SAS Super FREQ
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
Not applicable
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
SAS Super FREQ
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]

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 9648 views
  • 0 likes
  • 3 in conversation