PROC EXPORT putting some variables on separate rows in csv file

Accepted Solution Solved
Reply
Occasional Contributor NDS
Occasional Contributor
Posts: 10
Accepted Solution

PROC EXPORT putting some variables on separate rows in csv file

Hi,

I am exporting a dataset with proc export. The data set looks fine in the explorer view, but when I export it some of the variables are being put on separate rows.

In other words I have 1 row  in my dataset containing Date, Var 1, Var 2 , Var 3

When I export this I get two rows 1st row contains Date Var 1 and Var 2, and the 2nd row contains Var 3

Any idea why this happens? The variables that are landing up on the extra rows have been created inside an IF statement in a DATA step - not sure if that is the problem?

Thanks in advance.


Accepted Solutions
Solution
‎05-10-2012 09:01 AM
Occasional Contributor NDS
Occasional Contributor
Posts: 10

Re: PROC EXPORT putting some variables on separate rows in csv file

Ok - figured it out.

It was a dos2unix issue after all.

The file had been saved over again after I had run dos2unix on it. Running it again has resolved the problem.

Thanks


View solution in original post


All Replies
Respected Advisor
Posts: 3,799

Re: PROC EXPORT putting some variables on separate rows in csv file

Hard to say without more info.

Possibly

  • LRECL
  • embedded linefeed
  • program used to look at the file

What is your OS?

Try looking at the file in HEX mode in your editor.
or

data _null_;

    infile lrecl=32767 termstr=crlf; *Windows;

    input;

    list;

    run;

Frequent Contributor
Posts: 101

Re: PROC EXPORT putting some variables on separate rows in csv file

Are you looking at the file in Excel or in a text editor? If a text editor like Notepad, is it just wrapping to a new line?

Occasional Contributor NDS
Occasional Contributor
Posts: 10

Re: PROC EXPORT putting some variables on separate rows in csv file

Posted in reply to SAS_Bigot

Thanks,

I am running SAS 9.2 off a UNIX box, however I am creating input files in windows 7 (Excel) and viewing exported files in the same way.

It appears that the problem is actually coming from when I import the data:

DATA Work.dataSet;

     INFILE "myfile.csv";

     INPUT Date :MMDDYY10. numVar stringVar :$40.;

RUN;

myfile csv contains 3 columns a date (mm/dd/yyyy), a number (##.##) and a string (max 40 chars)

The import runs fine and viewing the dataset in SAS Explorer works fine.

However, if I export the resulting dataset, I find a ^M appended on the end of stringVar.

PROC EXPORT DATA = dataSet OUTFILE = "Output.csv" REPLACE; RUN;

Each row is something like - 01/02/2006,14.17,ARCHWAY^M

Though not all the rows!

I have triued dos2unix -ing my input files but to no avail.

Any suggestions?

Thanks

and this results in a new line between each row.

Solution
‎05-10-2012 09:01 AM
Occasional Contributor NDS
Occasional Contributor
Posts: 10

Re: PROC EXPORT putting some variables on separate rows in csv file

Ok - figured it out.

It was a dos2unix issue after all.

The file had been saved over again after I had run dos2unix on it. Running it again has resolved the problem.

Thanks


Occasional Contributor NDS
Occasional Contributor
Posts: 10

Re: PROC EXPORT putting some variables on separate rows in csv file

Actually no.

This solved one problem, but I am now using PROC GLM to estimate a fixed effect model and the Output file still contains these linefeeds!

PROC GLM DATA = DataSet1;

     CLASS stringVar;

     MODEL Var1 = Var2 Var 3 stringVar /SOLUTION;

     OUTPUT  OUT = ModelFit;

RUN;

If I export DataSet there are no linefeeds after the stringVar, but when I export ModelFit there are, and this causes linebreaks in my csv file.

Any idea how I can resolve this one?

Thanks


Frequent Contributor
Posts: 101

Re: PROC EXPORT putting some variables on separate rows in csv file

One problem could be related to how the data is transferred between unix and windows. In a windows text file, each line of data is terminated with a carriage return (CR) and line feed (LF). But in unix the line terminator is just a LF. I've had instances when someone ftp'ed files to me from windows to unix, and the files still contained the dos terminators (CRLF). That caused problems reading the data. On the infile statement, I added the option termstr=crlf so that SAS would recognize the dos terminators. Maybe that will work for you. I have a feeling that the embedded CR is affecting your output.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 3906 views
  • 0 likes
  • 3 in conversation