The SAS Output Delivery System and reporting techniques

Read Pipe Delimited File

Accepted Solution Solved
Reply
Super Contributor
Posts: 358
Accepted Solution

Read Pipe Delimited File

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.


Accepted Solutions
Solution
‎02-23-2012 02:31 PM
Esteemed Advisor
Posts: 6,912

Re: Read Pipe Delimited File

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;

View solution in original post


All Replies
Solution
‎02-23-2012 02:31 PM
Esteemed Advisor
Posts: 6,912

Re: Read Pipe Delimited File

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;

Super Contributor
Posts: 358

Read Pipe Delimited File

Art:

Once again making it look simple !

Thanks - and see you at TASS!

Esteemed Advisor
Posts: 6,912

Read Pipe Delimited File

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.

Super Contributor
Posts: 358

Read Pipe Delimited File

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: '';

Esteemed Advisor
Posts: 6,912

Read Pipe Delimited File

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.

Esteemed Advisor
Posts: 6,912

Read Pipe Delimited File

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;

Grand Advisor
Posts: 9,748

Read Pipe Delimited File

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.

Super User
Super User
Posts: 5,987

Read Pipe Delimited File

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

Esteemed Advisor
Posts: 6,912

Read Pipe Delimited File

Were you finally able to resolve this?

New User
Posts: 1

Re: Read Pipe Delimited File

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.

Post a Question
Discussion Stats
  • 10 replies
  • 15494 views
  • 0 likes
  • 5 in conversation