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!
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
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?
And what is the rule that connects these two observations?
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.