How to add value from variable of one observation to another observation

[wernie]

Trying to post this for the third time. Hoping the text shows...

I'm trying to add the values from one variable for one observation to another observation. I have the following:

FIPS5       char_state    char_county      yr       num_days

51015          51                015                2010          2

51017          51                017                2010          1

51019          51                019                2010          3

51021          51                021                2010          5

....                 ..                 ...                   ....              . (continued with all FIPS codes)

51515          51                515                2010          2

I want to add the num_days value from 51515 (2) to the num_days value for 51019 (3) and get the new total of 5 assigned to the 51019 row and delete 51515, so I'd end up with:

FIPS5        char_state       char_county     yr      num_days

51015           51                    015              2010       2

51017           51                    017              2010       1

51019           51                    019              2010       5 (new total)

51021           51                    021              2010       5

....                  ..                     ...                  ...           .  (continued with all FIPS codes)

51515 deleted from the table.

Thanks! 

[novinosrin]

Hi @wernie  Are you sure there is no content that follows the question? For example, a data sample of what you HAVE and what you WANT briefly explaning the business logic/requirement?

Or, if I am mistaken, is it just a casual theoretical question?

[wernie]

@novinosrin ugh, yes, there was much more to that post. I already tried posting it twice and something doesn't seem to be working properly. Strange because it showed up for me initially, but now it's blank.

Will try again in a different browser.

[Kurt_Bremser]

And what is the rule that connects these two observations?

[ballardw]

It really helps to provide data in the form of a data step so we can test code. Paste code and log entries into a code box opened on the forum with the </> or running man icons.

Here is one way which may be flexible to add other improbable FIPS codes.

data have;
  input FIPS5   $    char_state $   char_county $     yr       num_days ;
datalines;
51015          51                015                2010          2
51017          51                017                2010          1
51019          51                019                2010          3
51021          51                021                2010          5
51515          51                515                2010          2
;

proc format library=work;
value $fips5_
'51515' = '51019'
;
value $char_county
'515'='019'
;
run;

proc summary data=have nway;
	class fips5 char_state char_county yr;
	format fips5 $fips5_. char_county $char_county.;
	var num_days;
	output out=work.want (drop= _:) sum= ;
run;

You don't say if there are other variables involved. That might take creating a different summary and/or merging the results back onto the data.

Or you could have used a data step with if/then/else to assign the proper codes and then summarized.

Groups created by formats will be honored by most procedures. A side effect of a format + class statement is that the lowest value of the variable ends up in the output data. So since that is apparently what you wanted this works. Other combinations you may be better off with the data step if/then/else assigning the codes.

[LeonidBatkhan · Accepted solution]

You can do that as shown in the below code:

/* sort data in descending FIPS5 order */
proc sort data=A out=B;
   by descending FIPS5;
run;

/* calculate your sum */
data C;
   set B;
   retain TEMP;
   if FIPS5='51515' then
   do; /* remember num_days and _n_ for this observation */
      TEMP = num_days;
      call symputx('NDEL',_n_);
   end;
   if FIPS5='51019' then num_days = num_days + TEMP;
run;

/* delete observation */
data C1;
   set C;
   if _n_=&NDEL then delete;
run;

This solves the problem as you described it. If you need to generalize it you need to provide more general requirements. Or having this code example you might be able to figure it out yourself.

For more on doing calculations across different SAS data set observations see my blog post:

Hopping for the best - calculations across SAS dataset observations

[wernie]

@LeonidBatkhan thanks so much! That worked well.