topic Re: How to calculate sum by a variable and create a macro variable in SAS Programming

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

knveraraju91 — Thu, 02 Mar 2017 04:41:31 GMT

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;

The total value doesnot include placebo. Please help. Thank you

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

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

LinusH — Thu, 02 Mar 2017 09:02:07 GMT

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.

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

RW9 — Thu, 02 Mar 2017 09:22:42 GMT

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.

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

gamotte — Thu, 02 Mar 2017 13:31:29 GMT

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

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

knveraraju91 — Thu, 02 Mar 2017 15:37:55 GMT

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 needed:(Created 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

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

ballardw — Thu, 02 Mar 2017 16:21:22 GMT

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?

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

knveraraju91 — Thu, 02 Mar 2017 17:54:58 GMT

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.