Reading multiple lines from a single observation

[gleebglorb]

data empraw; 
infile empdat firstobs=2 dlm='09'x ;
input name : $6.  lastname : $6. @ ;
input score $ @@ ;
run;

I am having trouble with line hold specifiers.  

My input data looks like this (ignore the formatting issues like spacing)

FirstName LastName Score 

Sally    Mae      30       40       50    60 

John    Goe      30       40       50    60 

Matt     Miller      30       40       50    60 

Henry   Mae      30       40       50    60 

where score should be read in several times and First and Last name should be retained to output something like this 

Sally       Mae      30 

Sally       Mae      40

Sally       Mae      50

Sally       Mae      60

I'm currently trying this: 

data empraw; 

infile empdat firstobs=2 dlm='09'x ;

input name : $6.  lastname : $6. @ ;

input score $ @@ ;

run;

Right now, it doesn't hold the input buffer, and outputs like this:

Sally       Mae      30 

40           50        60

What am I doing wrong?

[PeterClemmensen]

Where is your input data stored?

[gleebglorb]

It's on my computer as a txt file.  The actual line is 

infile 'S:\rawdat\employee\employee.txt'

[PeterClemmensen]

I suppose this can be done with the input statement. Though, this seems more logical to me

data have(drop = score1 - score4);
input FirstName $ LastName $ score1-score4;
array s score1-score4;
do over s;
   score = s;
   output;
end;
datalines;
Sally Mae    30 40 50 60 
John  Goe    30 40 50 60 
Matt  Miller 30 40 50 60 
Henry Mae    30 40 50 60 
;

Result:

FirstName LastName score 
Sally     Mae      30 
Sally     Mae      40 
Sally     Mae      50 
Sally     Mae      60 
John      Goe      30 
John      Goe      40 
John      Goe      50 
John      Goe      60 
Matt      Miller   30 
Matt      Miller   40 
Matt      Miller   50 
Matt      Miller   60 
Henry     Mae      30 
Henry     Mae      40 
Henry     Mae      50 
Henry     Mae      60 

[gleebglorb]

Is there a way to do this using line hold specifiers like @ or @@? The actual data set is much much bigger than what I posted and is still being updated so I don't want to do it with datalines.  

[ballardw]

---

@gleebglorb wrote:

Is there a way to do this using line hold specifiers like @ or @@? The actual data set is much much bigger than what I posted and is still being updated so I don't want to do it with datalines.  

---

Datalines are just an example. You would use an infile statement to point to the data file.

If you always have 4 values to read perhaps:

data have;
input FirstName $ LastName $ @;
do i=1 to 4;
   input score  @;
   output;
end;
input;
drop i;
datalines;
Sally Mae    30 40 50 60 
John  Goe    30 40 50 60 
Matt  Miller 30 40 50 60 
Henry Mae    30 40 50 60 
;

If you have missing values on your infile statement you would use one of the options TRUNCOVER or MISSOVER.

[gleebglorb]

Would missover/truncover prevent missing observations from being read in? There are a few observations with less than four scores

[ballardw]

---

@gleebglorb wrote:
Would missover/truncover prevent missing observations from being read in? There are a few observations with less than four scores

---

Missover and truncover are both somewhat different approaches to tell SAS what to do when the expected data runs out before the input instruction is completed. The default for SAS is to attempt to finish reading from the next line, which would be reading non-score values into score and cause invalid data, character not fitting into numeric variables.

Use TRUNCOVER to assign the contents of the input buffer to a variable when the field is shorter than expected.

Use MISSOVER if the last field or fields might be missing and you want SAS to assign missing values to the corresponding variable.

Behavior is similar for many simple read items.

Example of without an option and with:

data defaulthave;
input FirstName $ LastName $ @;
do i=1 to 4;
   input score  @;
   output;
end;
input;
drop i;
datalines;
Sally Mae    30 40 50 60 
John  Goe    30 40 50 60 
Matt  Miller 30 40 
Henry Mae    30 40 50 60 
;

data have;
infile datalines missover;
input FirstName $ LastName $ @;
do i=1 to 4;
   input score  @;
   output;
end;
input;
drop i;
datalines;
Sally Mae    30 40 50 60 
John  Goe    30 40 50 60 
Matt  Miller 30 40 
Henry Mae    30 40 50 60 
;

If you run the first data step above your log will show something like:

NOTE: Invalid data for score in line 262 1-5.
NOTE: Invalid data for score in line 262 7-9.
RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+--
262        Henry Mae    30 40 50 60
FirstName=Matt LastName=Miller i=5 score=. _ERROR_=1 _N_=3
NOTE: SAS went to a new line when INPUT statement reached past the end of a line.

and the data will be incomplete. That note about "went to a new line" almost always means you have an issue between your expected data from the input statement(s) and the actual content.

The second data step yields what I think you would expect with two missing values on a record.

You can run the code with TRUNCOVER in place of MISSOVER to see what, if any difference, results.

Note: You are using list input. So if the "first" score is the one you think is missing the result will be read as the first actual score an the missing will appear at the end. If you need to know or keep a specific order of "missingness" then the solution shown by @PeterClemmensen would be a better choice.

[Ksharp]

data have;
infile datalines truncover;
input FirstName $ LastName $ value @;
do while(not missing(value));
 output;
 input value @;
end;

datalines;
Sally Mae    30 40 50 60 
John  Goe    30 40 50 60 
Matt  Miller 30 40 
Henry Mae    30 40 50 60 
;