Add Partially Blank Observation to Dataset

[jawhitmire]

SAS Version 9.4

Hi,

This is what I have...

data work.have1;
	input a b c;
datalines;
5 6 7
8 9 4
3 2 3
;
run;
data work.have2;
	input a b c;
datalines;
4 5 6
2 3 4
9 7 2
;
run;

proc sort data=work.have1; by a; run;
proc sort data=work.have2; by a; run;

data work.want;
	merge work.have1 work.have2 end=lastone;
	by a;
	Term=a*b*c;
	cum_soln+term;
	length cum_soln 8.;
	if lastone then additup=cum_soln;
run;

proc print data=work.want; title 'Add Observation for Total'; run;

It compiles without errors in 9.4, but the output has the total on the same line as observation (6).

Obs a b c Term cum_soln additup 
1 2 3 4 24 24 	. 
2 3 2 3 18 42	. 
3 4 5 6 120 162 . 
4 5 6 7 210 372 . 
5 8 9 4 288 660 . 
6 9 7 2 126 786 786 

I want an observation (7) that is completely blank with the exception of the final column where additup=786

The goal is to have outputs that cannot be misunderstood.

Please note that the actual program has much more data, is within a macro, and thus, does not include the first two data steps

creating datasets work.have1 and work.have2. 

Thank you in advance for helping.

Yours,

Jane

[tsap]

I believe this code will accomplish what you are looking for:

data work.want;
	merge work.have1 
		  work.have2 end=lastone;
	by a;
	Term=a*b*c;
	cum_soln+term;
	length cum_soln 8.;
	LENGTH additup 3;
	output;
	IF lastone THEN DO; 
		A = .;
		B=.;
		C=.;
		term=.;
		additup=cum_soln;
		output;
	END;
	DROP cum_soln;
run;

Hope this helps.

[ballardw]

IF lastone THEN DO; 
		call missing (A,B,C,term);
		additup=cum_soln;
		output;
	END;

Call missing is one of the few data manipulation functions that will work on both character and numeric variables and all at the same time.

[FreelanceReinh · Accepted solution]

Hi Jane,

Replace the IF-THEN statement by

output;
if lastone then do;
  _n_=cum_soln;
  call missing(of _all_);
  additup=_n_;
  output;
end;

I store the value of CUM_SOLN in the automatic variable _N_, which is not included in variable list _ALL_, so that the value for ADDITUP is still available after clearing the other variables (without needing a more specific variable list).