turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Learn SAS
- /
- Analytics U
- /
- SAS is Shifting Data to the Left

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-19-2017 01:04 PM

Okay, so I am a bit new to SAS and I am working on an internship project. I have a comma delimited excel file (.csv).However, where I have missing data (blank cells in excel) SAS is shifting variables to the left....I don't know if I am explaining this great....so here is the code I am using (I am leaving out the huge list of variables):

data CPH909.EMain;

infile '/folders/myfolders/CPH909/EMainTable.csv' firstobs=2 dlm=',' lrecl=32000 truncover;

length *I list all of my variables here because I need them to stay in order...but a few of them are long and need to be changed*;

input *this is where I have my huge list of variables...I double checked an they are all there and match what is in the length field above*;

run;

So, I don't get any areas, but see the attachments for what I see in excel v.s. what I see in SAS.

Any help would be highly appreciated!

When looking at the above data, you can see where the data has been shifted in both lines in SAS...the only reason I can think of this is that SAS is not recognizing the missing data in excel for some reason.....

Accepted Solutions

Solution

12-19-2017
02:09 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ScottSt

12-19-2017 01:15 PM - edited 12-19-2017 01:15 PM

First off, it is a good idea to get out of the habit of expecting Excel's display of CSV data to be "correct" or the "right way". Excel reformats values when showing the data.

You likely need the DSD option on the INFILE statement to tell SAS to read a missing value between consecutive delimiters. By default SAS treats consecutive delimiters as a "unit" or single delimiter.

All Replies

Solution

12-19-2017
02:09 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ScottSt

12-19-2017 01:15 PM - edited 12-19-2017 01:15 PM

First off, it is a good idea to get out of the habit of expecting Excel's display of CSV data to be "correct" or the "right way". Excel reformats values when showing the data.

You likely need the DSD option on the INFILE statement to tell SAS to read a missing value between consecutive delimiters. By default SAS treats consecutive delimiters as a "unit" or single delimiter.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

12-19-2017 02:11 PM

Thank you so much! I vaguely remember the dsd being discussed in some lesson...but I completely forgot about it. And thank you for the extra tip and excel's way of displaying a csv file; I appreciate it.

Ta!

- Scott

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ScottSt

12-19-2017 01:17 PM

CSV and Excel are not the same thing...and Excel doesn't always read CSV files correctly. Check your files with a text editor, not Excel.

ScottSt wrote:

Okay, so I am a bit new to SAS and I am working on an internship project. I have a comma delimited excel file (.csv).However, where I have missing data (blank cells in excel) SAS is shifting variables to the left....I don't know if I am explaining this great....so here is the code I am using (I am leaving out the huge list of variables):

data CPH909.EMain;

infile '/folders/myfolders/CPH909/EMainTable.csv' firstobs=2 dlm=',' lrecl=32000 truncover;

length *I list all of my variables here because I need them to stay in order...but a few of them are long and need to be changed*;

input *this is where I have my huge list of variables...I double checked an they are all there and match what is in the length field above*;

run;

So, I don't get any areas, but see the attachments for what I see in excel v.s. what I see in SAS.

Any help would be highly appreciated!

When looking at the above data, you can see where the data has been shifted in both lines in SAS...the only reason I can think of this is that SAS is not recognizing the missing data in excel for some reason.....

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ScottSt

12-19-2017 01:19 PM

Use PROC IMPORT with GUESSINGROWS=MAX on the file and then check the code in the log. Then you can compare it to your code and see what the differences are and where you may want to make changes.

ScottSt wrote:

Okay, so I am a bit new to SAS and I am working on an internship project. I have a comma delimited excel file (.csv).However, where I have missing data (blank cells in excel) SAS is shifting variables to the left....I don't know if I am explaining this great....so here is the code I am using (I am leaving out the huge list of variables):

data CPH909.EMain;

infile '/folders/myfolders/CPH909/EMainTable.csv' firstobs=2 dlm=',' lrecl=32000 truncover;

length *I list all of my variables here because I need them to stay in order...but a few of them are long and need to be changed*;

input *this is where I have my huge list of variables...I double checked an they are all there and match what is in the length field above*;

run;

So, I don't get any areas, but see the attachments for what I see in excel v.s. what I see in SAS.

Any help would be highly appreciated!

When looking at the above data, you can see where the data has been shifted in both lines in SAS...the only reason I can think of this is that SAS is not recognizing the missing data in excel for some reason.....

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ScottSt

12-19-2017 01:28 PM

When reading across a line of data from left to right, SAS treats multiple delimiters (in this case multiple commas) as if they mark just a single variable. To get SAS to treat each comma as marking a separate variable, you need to add DSD to the INFILE statement:

infile '/folders/myfolders/CPH909/EMainTable.csv' firstobs=2 dlm=',' lrecl=32000 truncover dsd;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ScottSt

12-24-2017 10:18 AM

Hi.

My guess is that you need to use the DSD INFILE option. The reason: By default, even if you change the delimiter to a comma, SAS treats multiple delimiters as a SINGLE delimiter. Using the DSD (delimiter sensitive data) is specifically designed to read CSV files where two commas together imply a missing value. Give that a try and let me know. By the way, is you use DSD, you do not have to use the DLM= option,