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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
NDS
Calcite | Level 5 NDS
Calcite | Level 5

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

6 REPLIES 6
data_null__
Jade | Level 19

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;

FloydNevseta
Pyrite | Level 9

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?

NDS
Calcite | Level 5 NDS
Calcite | Level 5

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.

NDS
Calcite | Level 5 NDS
Calcite | Level 5

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


NDS
Calcite | Level 5 NDS
Calcite | Level 5

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


FloydNevseta
Pyrite | Level 9

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.

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
  • 6 replies
  • 6881 views
  • 0 likes
  • 3 in conversation