topic Re: How to keep only the last observation of a summed group? in SAS Programming

How to keep only the last observation of a summed group?

JDDowell — Fri, 06 Sep 2019 21:58:44 GMT

data PI;
	Infile datalines dlm=',';
	input MonthOfService MonYY5. N_Youth $ ProgramIndex $;
	format MonthofService MonYY5.;
	datalines; 
JUL13,	6,	A2111
JUL13,	2,	A2111
JUL13,	20,	A2111
JUL13,	155,	A2106
JUL13,	10,	A2106
JUL13,	44,	A2109
JUL13,	7,	A2124
AUG13,	7,	A2111
AUG13,	2,	A2111
AUG13,	23,	A2111
AUG13,	167,	A2106
AUG13,	14,	A2106
AUG13,	46,	A2109
AUG13,	7,	A2124
AUG13,	5,	A2124

proc sort data=PI;
by ProgramIndex;
run;

data PI_numbers;
set PI;
By ProgramIndex;
if First.ProgramIndex then Num_Youth=0;
Num_Youth + N_Youth;
If Last.MonthOfService
run;

I'm trying to keep only the last observation after I have summed up N_Youth for the ProgramIndex. I can get the sum for each program index but I also get all previous sums value as well. I try to use

Last.MonthOfService

to only record the last value of a given month, but when I do my table contains no values. Is it the date format?

Re: How to keep only the last observation of a summed group?

Astounding — Fri, 06 Sep 2019 22:14:13 GMT

Replace

If last.MonthOfService;

Instead, use

If last.ProgramIndex;

Re: How to keep only the last observation of a summed group?

hashman — Fri, 06 Sep 2019 22:16:28 GMT

@JDDowell:

1. It is not clear from you code/description whether you want to sum within ProgramIndex, MonthOfService, or the latter within the former. Please show your suggested output data set related to your sample input.

2. Your addend N_Youth must be numeric, yet you input it as character. Try this input instead:

data PI ;                                                 
  input MonthOfService :MonYY5. N_Youth ProgramIndex :$5 ;
  format MonthofService MonYY5. ;                         
  cards ;                                                 
JUL13    6  A2111                                         
JUL13    2  A2111                                         
JUL13   20  A2111                                         
JUL13  155  A2106                                         
JUL13   10  A2106                                         
JUL13   44  A2109                                         
JUL13    7  A2124                                         
AUG13    7  A2111                                         
AUG13    2  A2111                                         
AUG13   23  A2111                                         
AUG13  167  A2106                                         
AUG13   14  A2106                                         
AUG13   46  A2109                                         
AUG13    7  A2124                                         
AUG13    5  A2124                                         
;                                                         
run ;

3. You cannot use last.MonthOfService if MonthOfService is not listed in the BY statement - and the input data set should be sorted accordingly.

4. You're missing a semicolon in the related subsetting IF statement.

5. I'd suggest you do it using proc MEANS/SUMMARY or SQL instead of the DATA step. But to show you how, one needs to have a clear picture of what you want to achieve. Go to #1.

Kind regards

Paul D.

Re: How to keep only the last observation of a summed group?

PGStats — Sat, 07 Sep 2019 03:46:55 GMT

I'm only guessing, but I think you mean to do this:

data PI ;                                                 
  input MonthOfService :MonYY5. N_Youth ProgramIndex :$5. ;
  format MonthofService MonYY5. ;                         
  cards ;                                                 
JUL13    6  A2111                                         
JUL13    2  A2111                                         
JUL13   20  A2111                                         
JUL13  155  A2106                                         
JUL13   10  A2106                                         
JUL13   44  A2109                                         
JUL13    7  A2124                                         
AUG13    7  A2111                                         
AUG13    2  A2111                                         
AUG13   23  A2111                                         
AUG13  167  A2106                                         
AUG13   14  A2106                                         
AUG13   46  A2109                                         
AUG13    7  A2124                                         
AUG13    5  A2124                                         
;

data want;
do until (last.programIndex);
    set PI; by MonthOfService ProgramIndex notsorted;
    num_youth = sum(num_youth, N_Youth);
    end;
keep MonthOfService ProgramIndex num_youth;
run;

proc print data=want noobs; run;

                             Month
                              Of       Program     num_
                            Service     Index     youth

                             JUL13      A2111       28
                             JUL13      A2106      165
                             JUL13      A2109       44
                             JUL13      A2124        7
                             AUG13      A2111       32
                             AUG13      A2106      181
                             AUG13      A2109       46
                             AUG13      A2124       12