topic Add total conditionally in datastep, proc summary or proc report in SAS Programming

Add total conditionally in datastep, proc summary or proc report

Q1983 — Wed, 10 Mar 2021 23:42:24 GMT

data have;
length catgy $25 month_ $5 tot_cnt 8 descr $25 descr_pct monthsum 8
tran_type $3 seq_id 8 ;
input catgy -- seq_id;
datalines;
CURRENT JAN20 33 STAYED 98 1 D 3
CURRENT JAN20 33 ROLLED 2 1 D 3
CURRENT FEB20 25 STAYED 2 2 D 3
CURRENT FEB20 25 ROLLED 4 2 D 2
;
run;

Produces the following

catgy	month_	tot_cnt	descr	descr_pct	monthsum	tran_type	seq_id
CURRENT	JAN20	33	STAYED	98	1	D	3
CURRENT	JAN20	33	ROLLED	2	1	D	3
CURRENT	FEB20	25	STAYED	2	2	D	3
CURRENT	FEB20	25	ROLLED	4	2	D	2

Desired result is to add a new row, place an F under tran_type and place the total count of the records under seq_id

catgy	month_	tot_cnt	descr	descr_pct	monthsum	tran_type	seq_id
CURRENT	JAN20	33	STAYED	98	1	D	3
CURRENT	JAN20	33	ROLLED	2	1	D	3
CURRENT	FEB20	25	STAYED	2	2	D	3
CURRENT	FEB20	25	ROLLED	4	2	D	2
						F	4

Re: Add total conditionally in datastep, proc summary or proc report

ballardw — Wed, 10 Mar 2021 23:56:17 GMT

This works for your example:

data want;
   set have end=last curobs=obsnum;
   output;
   if last then do;
      call missing (of catgy-- monthsum);
      seq_id=obsnum ;
      Tran_type='F';
      output;
   end;
run;

End option creates a temporary variable that is set to 1 (true) when the current observation is last.

The Curobs option creates a temporary variable with the observation number, i.e. the row number in the data.

Call Missing, if you have not encountered it, sets a list of variables to missing values. It is one of the few functions that works on both character and numeric values.

Re: Add total conditionally in datastep, proc summary or proc report

Q1983 — Thu, 11 Mar 2021 01:41:28 GMT

Thanks, I can use this in similar code. Appreciate this