Learning SAS? Welcome to the exclusive online community for all SAS learners.

SAS is Shifting Data to the Left

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

SAS is Shifting Data to the Left

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! 

 

grab1.pnggrab2.png

 

 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
a month ago
Super User
Posts: 11,752

Re: SAS is Shifting Data to the Left

[ Edited ]

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.

View solution in original post


All Replies
Solution
a month ago
Super User
Posts: 11,752

Re: SAS is Shifting Data to the Left

[ Edited ]

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.

New Contributor
Posts: 2

Re: SAS is Shifting Data to the Left

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

Super User
Posts: 20,671

Re: SAS is Shifting Data to the Left

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! 

 

grab1.pnggrab2.png

 

 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.....


 

Super User
Posts: 20,671

Re: SAS is Shifting Data to the Left

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! 

 

grab1.pnggrab2.png

 

 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.....


 

Super User
Posts: 5,699

Re: SAS is Shifting Data to the Left

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;

Regular Learner
Posts: 1

Re: SAS is Shifting Data to the Left

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,

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 357 views
  • 5 likes
  • 5 in conversation