Read Pipe Delimited File

[OS2Rules]

Hi All:

I previously created a output file that is pipe delimied file using the following code:

  ods csv file="p:\sas\reports\tabname.txt" options(delimiter='|');

  proc print label noobs data=work01;

  run;

  ods csv close;

  run;

I am using a new ODS CSV tagset that I also recently downloaded from the SAS interwebnet site.

Now I would like to read this file back into a subsequent SAS program.  Can I do it the same as I would for an Excel spreadsheet?

I don't know the number of columns in the table but the first row does contain the variable names.

I would like to use a PROC IMPORT because I have a large number of tables to read and each one is different.

Thanks in advance.

[art297 · Accepted solution]

Couldn't you just use?:

PROC IMPORT OUT= WORK.WANT

            DATAFILE= "p:\sas\reports\tabname.txt"

            DBMS=DLM REPLACE;

     DELIMITER='|';

     GETNAMES=YES;

     DATAROW=2;

RUN;

[OS2Rules]

Art:

Once again making it look simple !

Thanks - and see you at TASS!

[art297]

Since you mention TASS, I should point out for anyone who will be in or near Toronto on March 2nd, 2012 (next Friday), that we've got a fantastic agenda planned (see: http://www.sas.com/offices/NA/canada/downloads/UserGroups/TASS-Agenda/TASS_Agendas_March_2012.pdf )

And, what isn't mentioned in the agenda, is that:

1. The Statistics' breakout session is going to have a special presentation on:  Predictive modelling (response and categorical models) for non-continuous and continuous – Applying Bayesian (proc discrim) and classical (proc logistic) statistics to improve model performance

and

2. The Coder's Corner breakout session is going to get an advanced look at the April 2012 SGF paper by FriedEgg, KSharp and myself, titled "Sometimes One Needs an Option with Unusual Dates"

Looking forward to seeing/meeting you there.

[OS2Rules]

Art:

I shouldn't have been so quick ...  there is still something that needs to be worked out....

After the file have been read, the SAS table contains a number or 'blank' records at the bottom.  I know that this is caused by the PROC IMPORT. 

Since I don't know the variable names of any of the tables that I read, is there a simple way to delete these blank records so they don't corrupt my data?  Normally I would just use a WHERE varname ne: '';

[art297]

I would be concerned regarding exactly where the blank lines are coming from.

I would guess that there is something wrong with the file you are trying to import, e.g., not having complete records.

What I usually do in such situations is click on F4 to see what proc import actually ran.  It may just be a matter of adding or changing one of the options, such as using truncover rather than missover.

If you just get rid of blank lines you might end up with garbage, pretty looking garbage, but garbage nonetheless.

[art297]

Conversely, I just replicated your process and the original ods commands resulted in adding an extra blank record at the end.

The following is my test and the code I used to get rid of the last record:

data work01;

  set sashelp.class;

  if _n_ in (5,6,7) then do;

    call missing(weight);

    call missing(height);

    if _n_ eq 5 then do;

      call missing(age);

      call missing(name);

    end;

  end;

run;

ods csv file="c:\art\tabname2.txt" options(delimiter='|');

proc print label noobs data=work01;

run;

ods csv close;

PROC IMPORT OUT= WORK.WANT

            DATAFILE= "c:\art\tabname2.txt"

            DBMS=DLM REPLACE;

     DELIMITER='|';

     GETNAMES=YES;

     DATAROW=2;

RUN;

data want (drop=x);

  set want;

  x=cmiss(of _all_);

  if x ne 6; /*num vars +1*/

run;

[ballardw]

Considering that every PROC that generates print output puts one or more blank lines after the table in RTF, PDF or other formats I'd say the culprit is PROC PRINT.

When I tried to get rid of the blanks in other output the answer was no joy.

You may need to investigae either EXPORT or if the issue is formatted values, the create text with the formatted values and then Export.

[Tom]

I suspect it is caused by PROC PRINT.  Why not generate the output file using PROC EXPORT instead?

[art297]

Were you finally able to resolve this?

[Maheema]

Hello,

I did the same steps (code) for a pipe delimted text file. The variables are being read, but the observations are not. It is showing as observations =0, variables=3.

The contenst of my file:

lastName|firstName|Age|

abc|def|20|

hij|lmn|24|

Please help me resolve this issue.