topic Creating new numeric variable based on values of other variables in SAS Programming

Creating new numeric variable based on values of other variables

AgaWS — Tue, 15 Feb 2022 18:48:20 GMT

Hi guys,

I want to create a new numeric variable for each participant which is the sum of all menge values from this participant. Each participant has it's own unique Id which is the variable TN_NR and several values for menge. That's why there are several rows for one participant. Here is a part of my dataset:

I came this far with the SAS code:

data epic;
set epic;
newmenge = sum (menge);
run;

I am new to programming in SAS and I don't know how to tell SAS to summarize all values of menge for each participant into the new variable.

I appreciate every help!! Thanks in advance!!

Re: Creating new numeric variable based on values of other variables

Reeza — Tue, 15 Feb 2022 18:53:16 GMT

******************************************************;
*Add average value to a dataset;
*Solution 1 - PROC MEANS + Data step;
******************************************************;

proc means data=sashelp.class noprint;
    output out=avg_values mean(height)=avg_height;
run;

data class_data;
    set sashelp.class;

    if _n_=1 then
        set avg_values;
run;

proc print data=class;
run;

*Solution 2 - PROC SQL - note the warning in the log;
PROC SQL;
Create table class_sql as
select *, mean(height) as avg_height
from sashelp.class;
quit;

******************************************************;
*Add average value to a dataset - with grouping variables;
*Solution 1 - PROC MEANS + Data step;
******************************************************;
proc means data=sashelp.class noprint nway;
class sex;
    output out=avg_values mean(height)=avg_height;
run;

*sort data before merge;
proc sort data=sashelp.class out=class;
by sex;
run;

data class_data;
 merge class avg_values;
 by sex;


run;

proc print data=class_data;
run;

*Solution 2 - PROC SQL - note the warning in the log;
PROC SQL;
Create table class_sql as
select *, mean(height) as avg_height
from sashelp.class
group by sex;
quit;

@AgaWS wrote:

Hi guys,

I want to create a new numeric variable for each participant which is the sum of all menge values from this participant. Each participant has it's own unique Id which is the variable TN_NR and several values for menge. That's why there are several rows for one participant. Here is a part of my dataset:

I came this far with the SAS code:
data epic;
set epic;
newmenge = sum (menge);
run;
I am new to programming in SAS and I don't know how to tell SAS to summarize all values of menge for each participant into the new variable.

I appreciate every help!! Thanks in advance!!

Re: Creating new numeric variable based on values of other variables

PaigeMiller — Tue, 15 Feb 2022 19:10:47 GMT

Try PROC SUMMARY

proc summary data=epic nway;
    class tn_nr;
    var menge;
    output out=want sum=newmenge;
run;

If you want this sum inserted back into your original data set

data want2;
    merge epic want(drop= _freq_ _type_);
    by tn_nr;
run;

Re: Creating new numeric variable based on values of other variables

Reeza — Tue, 15 Feb 2022 18:54:21 GMT

Replace mean with SUM in your use case.

Re: Creating new numeric variable based on values of other variables

PaigeMiller — Tue, 15 Feb 2022 19:18:29 GMT

@AgaWS wrote:

I came this far with the SAS code:
data epic;
set epic;
newmenge = sum (menge);
run;
I am new to programming in SAS and I don't know how to tell SAS to summarize all values of menge for each participant into the new variable.

I appreciate every help!! Thanks in advance!!

Hopefully, this advice will help you. The SUM() function in a DATA step, and nearly all functions in a DATA step, work by performing the function across a row of values. If you wanted to sum many variables in a row, the SUM function is what you want.

But you want the sum to go down a column, not across a row. That's where you need to use PROCs, which (with a few rare exceptions), perform arithmetic down column(s).

Re: Creating new numeric variable based on values of other variables

AgaWS — Wed, 16 Feb 2022 08:56:27 GMT

Thank you so much for the help!! I could manage it perfectly with your advice.

Re: Creating new numeric variable based on values of other variables

AgaWS — Wed, 16 Feb 2022 08:57:01 GMT

Thank you so much for the support!!