DATA Step, Macro, Functions and more

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

Reply
Super Contributor
Posts: 272

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;

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

 

Super User
Posts: 5,256

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
Super User
Posts: 7,401

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: 272

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 neededSmiley SadCreated 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: 10,497

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: 272

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.

Regular Contributor
Posts: 194

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

Ask a Question
Discussion stats
  • 6 replies
  • 297 views
  • 2 likes
  • 5 in conversation