DATA Step, Macro, Functions and more

What if the middle data point is missing rather than the last with Truncover/Missover?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 130
Accepted Solution

What if the middle data point is missing rather than the last with Truncover/Missover?

Hi,

 

In my data set, missing data, even when using truncover or missover, is shown in the last column (in this case the 3rd), even though for the 5th observation the second column has the missing data. How do I resolve this so that the second data is shown as missing?

 

Here is the raw data:

90 80 98
78 88
65 66 69
92 94 96
78     79   /*Note that the second data point is missing*/

------------------------

/*Here is my code:*/

Libname Learn'/folders/myfolders/Learn' ;

Data Problem_21_3 ;
    infile '/folders/myfolders/Learn/Scores_List.sas' truncover ;   /*or Missover*/
    input Score1-Score3 : 2. ;
run ;

-------------------

Note that for the 5th observation, the Score2, not Score3, is missing. However, the output shows that Score3 is missing

 

Here is the Output:

          Score1   Score2  Score3

1908098 
27888. 
3656669 
4929496 
57879.

 


Accepted Solutions
Solution
‎03-30-2018 12:53 PM
Super User
Posts: 6,933

Re: What if the middle data point is missing rather than the last with Truncover/Missover?

Posted in reply to ManitobaMoose

If it's just too much trouble to specify the columns, you could try an approach that gets rid of the colons.  That's causing the problem as you scan from left to right and  SAS finds nothing there.  SAS just keeps on scanning till it finds something.  Here's another approach you could try, assuming that all variables are two digits long, and a single space appears between:

 

input (score1-score3) (2. +1);

 

The instructions on the right get repeated for all variables in the list.

View solution in original post


All Replies
Super User
Posts: 8,216

Re: What if the middle data point is missing rather than the last with Truncover/Missover?

Posted in reply to ManitobaMoose

In the fifth record simply insert a space-period-space between the first and second scores.

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 6,933

Re: What if the middle data point is missing rather than the last with Truncover/Missover?

Posted in reply to ManitobaMoose

Does your incoming data line up in the same columns each time?  If so, it's much safer to tell SAS which columns to read:

 

input @1 score1 2.   @4 score2 2.  @7 score3 2. ;

 

And that would handle the original question you asked as well.

 

 

Frequent Contributor
Posts: 130

Re: What if the middle data point is missing rather than the last with Truncover/Missover?

Posted in reply to Astounding

Ok, so for a simple data set like this I could simply add a period for Score2, or I could use column inputs. But this is really an exercise to learn what to do if I had a large data set, of, for example, 100 scores and 50 observations. In that case, the column would work, but does this situation preclude the use of list inputs, or is there a way around this? I guess that is the real question.

 

Thanks!

Solution
‎03-30-2018 12:53 PM
Super User
Posts: 6,933

Re: What if the middle data point is missing rather than the last with Truncover/Missover?

Posted in reply to ManitobaMoose

If it's just too much trouble to specify the columns, you could try an approach that gets rid of the colons.  That's causing the problem as you scan from left to right and  SAS finds nothing there.  SAS just keeps on scanning till it finds something.  Here's another approach you could try, assuming that all variables are two digits long, and a single space appears between:

 

input (score1-score3) (2. +1);

 

The instructions on the right get repeated for all variables in the list.

PROC Star
Posts: 629

Re: What if the middle data point is missing rather than the last with Truncover/Missover?

Posted in reply to ManitobaMoose

Try dlm=" " with DSD and truncover options in Infile. Make sure the missing places are left one blank.

Thanks,
Suryakiran
Frequent Contributor
Posts: 130

Re: What if the middle data point is missing rather than the last with Truncover/Missover?

Posted in reply to SuryaKiran

Ok, I tried that, but now the score2 and score3 are missing for the 5th observation, instead of just the second observation:

 

 

1908098 
27888. 
3656669 
4929496 
578.. 

 

 

Frequent Contributor
Posts: 130

Re: What if the middle data point is missing rather than the last with Truncover/Missover?

Posted in reply to ManitobaMoose
I meant to say:

Ok, I tried that, but now the score2 and score3 are missing for the 5th observation, instead of just score2.
PROC Star
Posts: 629

Re: What if the middle data point is missing rather than the last with Truncover/Missover?

Posted in reply to ManitobaMoose

Seems like you have more than two blanks between 78 and 79. It must have only two, since the data is delimited by blank DSD will treat two consecutive delimiters as missing. If you have more than 2 then another missing value is read. If your data pattern is in this way the best way is using comma delimiter. Use DSD with comma separated values, then two consecutive delimiters(A,,B) are treated as missing. 

Thanks,
Suryakiran
Respected Advisor
Posts: 3,867

Re: What if the middle data point is missing rather than the last with Truncover/Missover?

Posted in reply to ManitobaMoose

 

 

filename FT15F001 temp;
data miss;
   infile FT15F001 missover;
   input @;
   _infile_ = transtrn(_infile_,'     ',' . ');
   input x1-x3;
   parmcards;
90 80 98
78 88
65 66 69
92 94 96
78     79 
;;;;
   run;
proc print;
   run;

Capture.PNG

 

☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 133 views
  • 2 likes
  • 5 in conversation