How to work with data that has missing values

[ELISAE]

Hi, I have a data set of n=14, but the program only recognises 8 of them. I think the reason for this is the fact that some observations has no data on some parameters. How can I tell the programme that there sometimes are no data at the collumn we can call "time" for some observations?

Thanks in advance!
(I have a small data set as you can see). 

[ACBradley]

Hi!
How are you accessing the data? Is there a filter present that is causing only 8 out of 14 rows to show up?

[andreas_lds]

You should post the program and the log so that we have at least a little chance to see what happens.

[jklaverstijn]

Hi,

(I have a small data set as you can see)

In fact we see very little. Could you provide us with more details bout both data and the analysis you're trying to do? Some types of analysis require data to be non-missing, others assume zero or behave in a special way. So please more background like rows, columns, values, code etc.

Regards,

-- Jan.

[ELISAE]

I'm actually not allowed to post the data. 
(and yes, you can actually "see" that there are n=24).
I have modified and removed a lot before posting so I don't know if this gives you enough information.

Data ;
input Samplgroup cnr B$ Gr$ La lawx BWT WTFISIL DIsil DMSILpbw WETFICC DMICC DMIRTTOT TOT DMIpBWH Miconc ECMMX kgECMpTOTVDDMI MkprodpDMIS MYpT Wdr vfrs Wfrc Totw Cu time;
Datalines; data lines are written here
;

Proc print;
run;

*Data means;
Proc means;
run;

Proc sort; by Samplgroup;
Proc means; by Samplgroup;
run;

[ballardw]

---

@ELISAE wrote:

Hi, I have a data set of n=14, but the program only recognises 8 of them.

---

What is the exact symptom or symptoms that makes you believe the program only "recognizes 8 of them"?

You have to let us know what exactly you are doing and exactly what the result is.

The snippet of code you showed with a variable list, procs means, sort and print have nothing that would remove any records.

If you cannot share actual data you can make an example data set. Replace numeric values with a random value (SAS has several ways to do that), text values with different strings like XXXXX or YYYYY to provide different values. If you have dates or times that might be sensitive then replace them with some other valid date or time value.

[ELISAE]

I tried to make a similar, but smaller data set to send you, but then i worked out just fine... 

If going back to the bigger data set it sais: 

NOTE: Invalid data errors for file CARDS occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.

what does that mean? 

Since I have now inserted numbers where there should not be any numbers (no data), I understand that is not the problem in this case. 

[ELISAE]

it also sais:

NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
NOTE: The data set WORK.MARKER has 8 observations and 26 variables.
NOTE: The data set WORK.METHOD has 8 observations and 26 variables.
NOTE: The data set WORK.STUDY has 8 observations and 26 variables.
NOTE: DATA statement used (Total process time):
real time 0.14 seconds
cpu time 0.14 seconds

[ballardw]

---

@ELISAE wrote:

it also sais:

NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
NOTE: The data set WORK.MARKER has 8 observations and 26 variables.
NOTE: The data set WORK.METHOD has 8 observations and 26 variables.
NOTE: The data set WORK.STUDY has 8 observations and 26 variables.
NOTE: DATA statement used (Total process time):
real time 0.14 seconds
cpu time 0.14 seconds

---

Typically the first note means that when the INPUT statement is executing (reading data) that for some reason it has run out of values on the current line so by default the program goes to the next line to continue reading. Usually this is the result of either the data line having missing values or using a read method that actually reads two or more values incorrectly resulting in insufficient data.

One common solution is having an option of TRUNCOVER or MISSOVER on the INFILE statement. If you don't have one then add

infile datalines truncover.

which will at least not read past the end of the line. If you have missing values then that is a likely cause as well but the solution depends. Here is an example (borrowed and edited from another question). run this an note similar messages and then look a the resulting data set.

data have;
input customer score $  sum_loan  interest;
cards;
1  a  100  5
2  a  200  4
3  a  300  4
4  b       3.5
5  b  500  2.8
6  c  100  5
;
run;

The intent is the third value on line 4 is to be missing. BUT there is nothing in the input or infile (not used) to tell so. Be default list input will read until the next value is found. If there were a period in place of the value then things read correctly:

data have;
input customer score $  sum_loan  interest;
cards;
1  a  100  5
2  a  200  4
3  a  300  4
4  b  .    3.5
5  b  500  2.8
6  c  100  5
;
run;

So from these Notes in the log your "symptom" was "DATA was not read as intended with  missing values not actual missing but having  incorrect values and some variables were read from the wrong line".

Unless you have a large amount of data I would recommend replaces spaces with periods to indicate a missing value in data lines.

Another option is use the DSD option on an infile but that has some other quirks depending on how your actual data you cannot show is laid out.

[ACBradley]

NOTE: Invalid data errors for file CARDS occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.

Are you using a DATA step to read in the file?  It sounds like it errored around line 8 (out of 24) which is why it was not able to read in the file.  From your example, it appears to be TAB delimited.  Check to make sure you have a consistent set of values (it appears you have 26 columns per row) for each header.   It's possible you have a single value that is tab separated and needs quotes (as an example) or two null values that need separator (tab) between them. 

Here is an SGF paper on options to read a file into SAS.

Clark

[ELISAE]

Thank you. I have gone throguh the whole data set to put the right amount of blanks etc, but there is no difference. I used the copy paste when I insterted the data (from an excel sheet). However, since there are different amounts of numbers within several of the observations there are somethimes small shiftings in the data, but it is still the right amount of space inbetween them.

[ballardw]

---

@ELISAE wrote:

Thank you. I have gone throguh the whole data set to put the right amount of blanks etc, but there is no difference. I used the copy paste when I insterted the data (from an excel sheet). However, since there are different amounts of numbers within several of the observations there are somethimes small shiftings in the data, but it is still the right amount of space inbetween them.

---

Go to your original Excel file.

Use the menu options to do a File-Save As to a CSV file.

Use proc import or the import wizard to read that file.

Blanks are very poor ways to separate much data as the options to deal with them are a tad odd and most people don't pay close attention. A delimited file with an actual character is easier for you to see and understand and CSV is very common. The Proc Import or wizard will likely not have any issues with keeping things consistent and managing "blank" values unless the values are in different column in you spreadsheet.

[ELISAE]

I have now solved my problem!
Thank you for your help!