BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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

 

6 REPLIES 6
LinusH
Tourmaline | Level 20

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

knveraraju91
Barite | Level 11

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

ballardw
Super User

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?

knveraraju91
Barite | Level 11

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.

gamotte
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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