topic Re: Problem with tab delimited data file in SAS Programming

Problem with tab delimited data file

CH33 — Wed, 19 Jun 2019 20:26:52 GMT

Good day, friends, I am a novice with SAS and I need your help! A colleague sent me a SAS-based program he developed and a tab-delimited data file (.dat) for use with his program. He specified that the data MUST be in this format. I thought I was following his instructions to the letter - tab delimited, .dat. However, I was able to get his data file to work with his program but not my data file. I keep getting a warning that there were missing values; however, I don't see any missing values :(. I imported both files into SAS v 9.4 to see if I could compare the two files. I thought, perhaps, if I could compare the two files, I would be able to fix mine. The results were fascinating. SAS said my file had the correct number of cases and the presumed number of variables. However, SAS said his had the correct number of cases but only one variable!!! He clearly had more than one - according to my calculations he had 15!

I have attached the results from the SAS log if anyone is interested in looking at it.

Your advice on why the two files are different and how to get mine to be more compliance is most welcome.

Thanks so much!

Re: Problem with tab delimited data file

snoopy369 — Wed, 19 Jun 2019 20:48:57 GMT

I think it's going to be hard to tell you exactly what's going on here without a bit more information about what you're doing. Some suggestions: post your code in your post (as text in the post, not as an attachment); describe the file (what it has in it), and describe what's wrong with your file. I'm not sure that "missing values" is a problem - it depends on the code.

Re: Problem with tab delimited data file

CH33 — Wed, 19 Jun 2019 20:56:59 GMT

Hello Snoopy 369 and thank you for your reply. You have hit part of the nail squarely on the head. I do not know what the problem is with the file - I have no idea! All I get is there are missing values. I can send you my .dat file but I do not feel that I can share my colleagues file because I do not have his permission.

Re: Problem with tab delimited data file

Reeza — Wed, 19 Jun 2019 21:47:53 GMT

Your code to import said file? File seems fine at first glance so likely one of you is reading it wrong. Please show both sets of code.

Re: Problem with tab delimited data file

Tom — Thu, 20 Jun 2019 02:55:41 GMT

I imported both files into SAS v 9.4

This does not really tell us what you did. Did you run a PROC IMPORT step? Where is the code (log) of what you ran?

In general if PROC IMPORT read his file as only having one variable then that means you did not tell SAS to use the right delimiter character.

It is also possible that someone edited the text file with a program (like the SAS program editor) that changed the tabs into spaces.

Re: Problem with tab delimited data file

PGStats — Thu, 20 Jun 2019 04:44:08 GMT

The following code read your data just fine:

proc import file="&sasforum.\datasets\Begg.txt" out=begg dbms=tab;
getnames=no;
run;

Your collegue's file probably had its tab characters replaced with spaces, somewhere along the way.

You could read both files with simple list input, using option expandtabs:

data begg;
infile "&sasforum.\datasets\Begg.txt" expandtabs;
input var1-var23;
run;

Re: Problem with tab delimited data file

ballardw — Thu, 20 Jun 2019 22:19:31 GMT

Please paste the code you used to read the data file into a code box opened using the forum's {I} or "running man" icons.

Note that your conversion of the log to picture in that DOCX format cut off lines as well as not actually showing the code. Also paste LOG entries the same way into a code box. Pictures are a lousy way to share code because we can't copy and make edits to what might be correct or even highlight text to bring things to attention.

The bit in that picture that says

The data set WORK.ADULT has 683 observations and 1 variables.

makes be very suspicious that the delimiter in the data was not the one used on the infile statement.

And it appears that you may have used PROC import which is not reliable for duplicating variable properties because each time you use proc import the procedure guesses about the content.

If you used PROC import then paste the ENTIRE Proc Import code as well as the data step generated by the proc that appears in the log.

If the data is not particularly sensitive copy 5 to 10 rows, including the header and paste them into a code box opened with the {I} icon so we can see what is actually in the data to compare with your code.

Do not paste directly into the message window. The forum software will reformat text removing blanks and possibly replacing tabs as well as sometimes inserting HTML tags.

Re: Problem with tab delimited data file

Doc_Duke — Thu, 20 Jun 2019 18:51:54 GMT

CH33,

Another approach might be to use an editor that show the actual ASCII codes (e.g. UltraEdit). Then you can truly see the differences in the files.