BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
DrAbhijeetSafai
Lapis Lazuli | Level 10

Dear All,

 

Greetings!

 

I have a dataset of in which one large variable is recently added. This newly added variable was not getting read properly so I used termstr=crlf and that csv file got read properly. It had values in excel at multiple levels (one can enter them by using alt+enter) and only first line was getting read. I used termstr=crlf  and the issue got solved. Now I am able to read and process those values (maximum length is somewhat 1000 characters) in that column properly.

 

When I am trying to write those values after processing to an external csv file, that is causing a problem. The problem is that where there are many lines in that row, only first line is getting printed in csv properly at that column and remaining values are coming to the first column and all other values are blank for those rows. Meaning only large values are shifted from their position to the first column in the csv.

 

I would like to know if there is any option like termstr=crlf  for exporting a file? I mean if termstr=crlf  can be used for importing, can similar option be used for exporting? Because the problem is similar in nature. While importing I was not able to read the values on lines below the first line (which has got solved by using termstr=crlf ) and now the issue is that I am not able to write values in such a way that all the lines are in the same row (and column). 

 

In the dataset, the values are perfect. The issue is with proc export in my opinion. 

 

Thanks in advance!

 

P.S. - I have attached a file of some sample output. Please look at the first column of order number. Values of a column have come to this first column instead or order number. 

 

- Dr. Abhijeet Safai

Dr. Abhijeet Safai
Certified Base and Clinical SAS Programmer
Associate Data Analyst
Actu-Real
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

If Termstr=CRLF "fixed" the read problem it is highly likely that there are linefeed characters that you just don't see. Viewing the data set with the table viewer and/or proc print output in the results window will not show this.

 

I suggest that you try this code to see the values that contain line feeds.

data examine;
   set <your data set name goes here> (keep=<name of the problem variable>);
   where index(<name of problem variable>,'0A'x)>0;
run;

Fix is simple

data new;
   set have;
   var=compress(var,'0A'x);
run;

View solution in original post

6 REPLIES 6
Reeza
Super User

How are you checking the CSV file/columns? In Excel or in a text editor?

 

Have you tried opening Excel, importing the file via the import data and specifying the delimiters as CSV? Sometimes that works better than how it automatically reads a file. 

 


@DrAbhijeetSafai wrote:

Dear All,

 

Greetings!

 

I have a dataset of in which one large variable is recently added. This newly added variable was not getting read properly so I used termstr=crlf and that csv file got read properly. It had values in excel at multiple levels (one can enter them by using alt+enter) and only first line was getting read. I used termstr=crlf  and the issue got solved. Now I am able to read and process those values (maximum length is somewhat 1000 characters) in that column properly.

 

When I am trying to write those values after processing to an external csv file, that is causing a problem. The problem is that where there are many lines in that row, only first line is getting printed in csv properly at that column and remaining values are coming to the first column and all other values are blank for those rows. Meaning only large values are shifted from their position to the first column in the csv.

 

I would like to know if there is any option like termstr=crlf  for exporting a file? I mean if termstr=crlf  can be used for importing, can similar option be used for exporting? Because the problem is similar in nature. While importing I was not able to read the values on lines below the first line (which has got solved by using termstr=crlf ) and now the issue is that I am not able to write values in such a way that all the lines are in the same row (and column). 

 

In the dataset, the values are perfect. The issue is with proc export in my opinion. 

 

Thanks in advance!

 

- Dr. Abhijeet Safai


 

DrAbhijeetSafai
Lapis Lazuli | Level 10

@Reeza , I am checking them in excel file. I have just attached the sample of the output. 

 

I did not understand what you mean to say by importing the file for this issue. The issue for import is solved and this is issue with export. Kindly let me know if any other information is required for this. 

 

Many thanks for your response.

 

- Dr. Abhijeet Safai

Dr. Abhijeet Safai
Certified Base and Clinical SAS Programmer
Associate Data Analyst
Actu-Real
Reeza
Super User

Excel is automatically using both of those as delimiters when reading the file, assuming you're just double clicking the file. 

 

Another way to get around this should also be the method I've suggested. 

To Import a CSV rather than Open it in Excel see the link below for instructions. 

https://www.fcc.gov/general/opening-csv-file-excel

 

  • Open Excel
  • Click New, then click on Blank workbook
  • Click on the Data tab
  • Click Get External Data From Text
  • Navigate to the CSV-formatted file saved on your system, select it, then click Import (When browsing to find the file, set browser to look for All Files or for Text Files)
  • This will open the Text Import Wizard
  • In Import Step 1 of 3 – Click on Delimited, then click Next
  • In Import Step 2 of 3 – Check the Comma box, then click Next
  • In Import Step 3 of 3 – Select Text and click Finish, then OK

@DrAbhijeetSafai wrote:

@Reeza , I am checking them in excel file. I have just attached the sample of the output. 

 

I did not understand what you mean to say by importing the file for this issue. The issue for import is solved and this is issue with export. Kindly let me know if any other information is required for this. 

 

Many thanks for your response.

 

- Dr. Abhijeet Safai


 

 

DrAbhijeetSafai
Lapis Lazuli | Level 10

Thank you @Reeza !

 

- Dr. Abhijeet Safai

Dr. Abhijeet Safai
Certified Base and Clinical SAS Programmer
Associate Data Analyst
Actu-Real
ballardw
Super User

If Termstr=CRLF "fixed" the read problem it is highly likely that there are linefeed characters that you just don't see. Viewing the data set with the table viewer and/or proc print output in the results window will not show this.

 

I suggest that you try this code to see the values that contain line feeds.

data examine;
   set <your data set name goes here> (keep=<name of the problem variable>);
   where index(<name of problem variable>,'0A'x)>0;
run;

Fix is simple

data new;
   set have;
   var=compress(var,'0A'x);
run;
DrAbhijeetSafai
Lapis Lazuli | Level 10

That was just fantastic @ballardw ! It solved the issue! I am so happy to get to know the answer even though I was not able to articulate the problem well in my opinion. But when someone can articulate the problem well, they can solve it as well on their own. So inability to articulate it and inability to solve it are the same things in that sense. 

 

The skill of the answerer is to identify it even though it is not well articulated! I am so glad that you possess that great ability. I am grateful to you.

 

Thank you very much!

 

- Dr. Abhijeet Safai

Dr. Abhijeet Safai
Certified Base and Clinical SAS Programmer
Associate Data Analyst
Actu-Real

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 983 views
  • 5 likes
  • 3 in conversation