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
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;
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
@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
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
@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
Thank you @Reeza !
- Dr. Abhijeet Safai
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;
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
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!
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.