## How to calculate sum by a variable and create a macro variable

Super Contributor
Posts: 325

# How to calculate sum by a variable and create a macro variable

Dear,

In the data one, the variables are Number of subjects,trtment and visit.

I need to create macro variables with each value of NS  by visit.

Someone suggested me the following code for similar work. But in this situation I need to calculate for total value by visit and create a macro variables for each visit;

With my code I am getting only one total value with ns=166. I need to have two total values with ns=83 for each visit.

data one;
input ns trt\$ visit\$;
datalines;
18 15 wk4
38 30 wk4
10 37 wk4
17 72 wk4
19 place wk4
18 15 wk1
38 30 wk1
10 37 wk1
17 72 wk1
19 place wk1
;

data two;
length ma \$100;
set one;
trt=strip(trt);
if trt='15' then ma='A'||visit;
if trt='30' then ma='B'||visit;
if trt='37' then ma='C'||visit;
if trt='72' then ma='D'||visit;
if trt='Place' then ma='E||visit;
run;

proc sort data=two;
by visit;
run;

data three;
set two end=done;
by visit;
if trt in ('15', '30', '37' '72') then Total + ns;
output;
if done;
ns = TOTAL;
trt='Total';
ma='F'||visit;
output;
drop Total;
run;

data _null_;
set three;
call symputx(ma,ns);
run;

output needed:

&awk4  &bwk4  &cwk4  &dwk4  &ewk4  &fwk4

18          38         10        17        19        83

&awk1  &bwk1  &cwk1 &dwk1  &ewk1  &fwk41

18          38         10        17        19        83

Super User
Posts: 5,922

## Re: How to calculate sum by a variable and create a macro variable

To get to initial requirement, why do you need a bunch of macro variables.

Creating a cluster of these can be a sign of a fault approach, or trying to use hard coded problems with macro, instead of using a proper data model and dynamic reporting tools.

Data never sleeps
Super User
Posts: 9,866

## Re: How to calculate sum by a variable and create a macro variable

Post an example of what you want out, creating endless macro variables is a really bad idea.  You can doa sum really simple using proc summary, or sum() group by in SQL.  The rest of the code there doesn't make sense.

Super Contributor
Posts: 325

## Re: How to calculate sum by a variable and create a macro variable

Thank you very much for your time.

I am trying to get a data set for a table that I need to create.

The following data set I need to created using proc means. But the n values created by proc means are not the values I need for for table.

The n values are (ns) distinct id values which I calculated using proc sql (data one).

I  ran proc means for the stat values and proc sql for number of distinct subjects.

data one; (created by sql)
input ns trt\$ visit\$;
datalines;
18 15 wk4
38 30 wk4
10 37 wk4
17 72 wk4
19 place wk4
18 15 wk1
38 30 wk1
10 37 wk1
17 72 wk1
19 place wk1
;

dataset output neededCreated by proc means). The n values  are not useful for my table.

visit   trt           n   mean median min    max   SD

wk1    15         2     5         5        3      7      2.82

wk1    30         2     6.5     6.5       6      7      0.7

wk1    37         2     5         5        3      7      2.82

wk1    72         2     6.5     6.5       6      7      0.7

wk1   placebo   2      8       8          7      9     1.4

wk4    15          2     5         5        3      7       2.82

wk4    30          2     6.5     6.5       6      7      0.7

wk4     37          2      8       8          7      9     1.4

wk4    72            2     6.5     6.5       6      7      0.7

wk4     placebo    2      8       8          7      9     1.4

Table output needed;

wk1(pul)

15                                    30                                    37                  72             placebo     Total

n(ns )                                  18                                     38                                   10                  17                19            83

mean(SD)                              5(2.82)                            6.5(0.7)                           8(1.4)             6.5(0.7)           8(1.4)

median                                  5                                     6.5                                 8                   6.5                 8

range(min,max)                      3, 7                                 6, 7                                7, 9                6.5                  8

wk1(pul)

15                                    30                                    37                  72             placebo     Total

n   (ns)                                   18                                     38                                   10                  17                19            83

mean(SD)                              5(2.82)                            6.5(0.7)                           8(1.4)          6.5(0.7)            8(1.4)

median                                  5                                     6.5                                 8                 6.5                  8

range(min,max)                      3, 7                                 6, 7                                7, 9          6.5                      8

Super User
Posts: 13,950

## Re: How to calculate sum by a variable and create a macro variable

I would strongly suggest looking into the report procedures. For instance something like:

```proc tabulate data=one format=best6.;
class trt visit;
var ns;
table visit,
ns*(sum mean std median range max min),
trt;
run;```

Since you did not have more than one value for combinations of trt and visit it is not possible to duplicate the results you show as there cannot be a standard deviation when there is only one value. And do you want Range (the numeric difference between max and min) or the max and min values?

Super Contributor
Posts: 325

## Re: How to calculate sum by a variable and create a macro variable

I think i donot need to calculate macro. After calculating total(ns), i used Proc Transpose procedure to get my output. Thanks you all for the support.

Super Contributor
Posts: 359

## Re: How to calculate sum by a variable and create a macro variable

[ Edited ]

Hello,

You only compute your total when all the dataset is processed (end=done will result in done being 1

at the end of the dataset). If you want to get a total for each visit, you have to use the "by visit" statement

(OK in your program) which will create a variable last.visit indicating the last observation for a given visit value.

So Your data three becomes :

``````data three;
set two;
by visit;    if first.visit then total=0; /* EDIT */
if trt in ('15', '30', '37' '72') then Total + ns;
output;
if last.visit;
ns = TOTAL;
trt='Total';
ma='F'||visit;
output;
drop Total;
run;``````

Edit : I forgot to reset total for each visit

Discussion stats
• 6 replies
• 1022 views
• 2 likes
• 5 in conversation