topic Re: How to add value from variable of one observation to another observation in SAS Programming

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

wernie — Fri, 27 Mar 2020 19:45:29 GMT

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!

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

novinosrin — Fri, 27 Mar 2020 19:18:11 GMT

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?

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

wernie — Fri, 27 Mar 2020 19:27:32 GMT

@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.

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

Kurt_Bremser — Fri, 27 Mar 2020 20:44:52 GMT

And what is the rule that connects these two observations?

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

ballardw — Fri, 27 Mar 2020 21:05:48 GMT

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.

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

LeonidBatkhan — Sun, 29 Mar 2020 00:33:18 GMT

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

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

wernie — Mon, 30 Mar 2020 17:47:50 GMT

@LeonidBatkhan thanks so much! That worked well.