SAS Code Help

[State2000]

I am relatively new to SAS, and was hoping someone could help me.

I have the following variables--Name, Test, and Result in a spreadsheet that looks similar to this:

Name Test Result
John Doe Math Exam Pass
John Doe English Exam
John Doe History Exam

The results for English Exam and History Exam are blank. I need them to say "Pass" as well. If any of John's test results say "Pass", then any test that has a blank result must also say "Pass". The same applies if one of his test results were to say "Fail".

The spreadsheet has a list of different names with many tests and results, and each name is listed several times for each test. The results for each exam will should all be made the same for each person.

Can someone please give me an example of the SAS code I need to use to fill on the blanks on the spreadsheet? Thanks so much.

 

[shenniko]

You can use First.Variable to do this. But this will only work if the Names are all together (e.g sorted by name).

Data Results;
	set Dataset;
	by name;
	retain prev_result;

	If first.name then
		do;
			prev_result = result;
		end;
	Else
		do;
			If Result = "" then
				do;
					Result = prev_result;
					Prev_result = Result;
				end;
			Else
				do;
					Prev_result = Result;
				end;
		end;
run;

 

[kdt82]

Hi,

By spreadsheet do you mean SAS dataset or Excel spreadsheet?

If Excel, then why not handle it in VBA?

In SAS you have a few options. First, let's modify your sample data to represent your spec better:

data have;
   infile cards missover;
   input Name $1-10 Test $12-25 Result $26-30;    
cards; 
John Doe   Math Exam     
John Doe   English Exam  Pass
John Doe   History Exam  
Mary Doe   Math Exam     Fail
Mary Doe   English Exam  
Mary Doe   History Exam  
   ;run;

If it's a fairly small dataset then the following should do the trick.

proc sort data=have out=sorted; 
   by name descending result;
   run;

data wantx;
   retain overall;
   set sorted;
   by name descending result;
   if first.name then overall=result;
   run;

proc print;run;

Just for interest there is also a structure called a double DOW loop that handles this sort of problem well. We could eliminate the need for a sort using this:

data want1;
   do _i_=1 by 1 until(last.name);
      set have;
      by name;
      if ^missing(result) then overall=result
      end;
   do _n_=1 to _i_;
      set have;
      output;
      end;
   run;
proc print;run;

HTH

 

[State2000]

Thanks to both of you very much. This helped me alot!