BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
OS2Rules
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

10 REPLIES 10
art297
Opal | Level 21

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
Obsidian | Level 7

Art:

Once again making it look simple !

Thanks - and see you at TASS!

art297
Opal | Level 21

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
Obsidian | Level 7

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
Opal | Level 21

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
Opal | Level 21

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
Super User

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
Super User Tom
Super User

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

art297
Opal | Level 21

Were you finally able to resolve this?

Maheema
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 10 replies
  • 61689 views
  • 1 like
  • 5 in conversation