topic Re: Making a new accumulating column by another grouped variable in New SAS User

Making a new accumulating column by another grouped variable

cpcb123 — Thu, 15 Dec 2022 06:26:12 GMT

Hello,

I am still quite new to SAS, so I'm looking for help. I'm trying to create a new variable that accumulates a numeric variable, Data Value, and is specific to the year 2020 for each state. My current data has multiple entries for each state for 2020 because there are observations for each month. I'm not interested in the months, just the year totals per state. My current code is:

data FinalOutput;
set Final.opioiddeaths;
where Indicator='Number of Drug Overdose Deaths';
keep State Indicator Year 'Data Value'n TotalDeaths;
by State;
if Year='2020' then output;
retain TotalDeaths 0;
TotalDeaths=TotalDeaths+'Data Value'n;
run;

The output shows only the observations for 2020. However, my new variable TotalDeaths is an accumulation of Data Values from all years. Is there a way in the data step for only the TotalDeaths in 2020 to accumulate by State? If this changes anything, I considered grouping it by Year but I need to do it by State as I am using it to merge tables later on.

Thank you

Re: Making a new accumulating column by another grouped variable

PeterClemmensen — Thu, 15 Dec 2022 06:45:29 GMT

@cpcb123 , can you show us som esample of your data? Makes it much easier to provide a usable code answer.

Re: Making a new accumulating column by another grouped variable

cpcb123 — Thu, 15 Dec 2022 07:09:19 GMT

This is my current output. Is there anything else I can send to help as well?

Re: Making a new accumulating column by another grouped variable

PeterClemmensen — Thu, 15 Dec 2022 07:30:04 GMT

So you want all observations from your original data set, but you want to accumulate the variable TotalDeaths when year = 2020. Is that correct?

Otherwise, please be very specific about what your data looks like (preferably posted as a data step) and your desired result.

Re: Making a new accumulating column by another grouped variable

cpcb123 — Thu, 15 Dec 2022 07:42:39 GMT

Yes, that is correct. However, would it be easier to assign TotalDeaths as a sum of Data Value for each state in 2020? I'm sorry I know that is not my original question

Re: Making a new accumulating column by another grouped variable

PeterClemmensen — Thu, 15 Dec 2022 08:00:15 GMT

It depends on what you want to do? If your desired result is a simple sum grouped by State for the year 2020, then there is no need for an accumulated variable in a data step.

Re: Making a new accumulating column by another grouped variable

cpcb123 — Thu, 15 Dec 2022 08:14:50 GMT

Yes true. How would I create that sum then?

Re: Making a new accumulating column by another grouped variable

PeterClemmensen — Thu, 15 Dec 2022 08:24:37 GMT

Needless to say, this code is untested since I do not have workable data.

See if you can use this as a template.

proc summary data = Final.opioiddeaths nway;
   where year = 2020;
   class state;
   var DataValue;
   output out = want sum =;
run;