How can I Precisely ask the question? This may be a little elaborate. Thank you for reading and for your patience.
I am creating a table for tests for N(Counts), Means, and SD of change (CHG) at each visit. Additionally, we make the same stats for 'Base" at the 'Baseline' visit. This is simple so far for me by using the 'Proc Means.' However, I need to create an additional "Baseline" stats Row ( indicated in "Orange cells" Ex: "Baseline (v1)) by checking how many subjects there were at that visit and calculating the Baseline Stats for that subjects only.
In the figure: I manually presented the Baseline stats for blood and urine tests before the visit1 because I knew there were 4' subjects at both visits. But How can I achieve it programmatically? If you see visit 2, there are 3 subjects. So I expect the Baseline stats of those three subjects for the Baseline Visit.
How I want to display
Note: the example is given to match the best of my requirement. There is flexibility in terms of terminology and how we display
data have;
input sid visit$ test$ val base chg;
datalines;
101 Baseline blood 20 20 .
101 Baseline urine 10 10 .
102 Baseline blood 25 25 .
102 Baseline urine 10 10 .
103 Baseline blood 20 20 .
103 Baseline urine 20 20 .
104 Baseline blood 30 30 .
104 Baseline urine 10 10 .
101 Visit1 blood 10 20 -10
101 Visit1 urine 20 10 10
102 Visit1 blood 10 25 -15
102 Visit1 urine 25 10 15
103 Visit1 blood 25 20 5
103 Visit1 urine 30 20 10
104 Visit1 blood 10 20 -10
104 Visit1 urine 30 30 0
101 Visit2 blood 40 20 20
101 Visit2 urine 50 10 40
102 Visit2 blood 60 25 35
102 Visit2 urine 70 10 60
103 Visit2 blood 60 20 40
103 Visit2 urine 70 20 50
;
Run;
Proc means data= have nway missing noprint;
class test visit ;
var base;
where visit = "Baseline";
output out= all_base (drop= _:) n=n mean=mean std=sd ;
run;
Proc means data= have nway missing noprint;
class test visit ;
var base;
where visit ^= "Baseline";
output out= byvisit (drop= _:) n=n mean=mean std=sd ;
run;
data all;
set all_base byvisit;
run;
ods listing;
proc print;
run;
ods listing close;
Thank you for your inputs.
Since you ask to compute "Baseline (Vn)" who has corresponding subjects to the specific visit, I think you have to intro conditional computation. Here is an easy way:
proc sort data=have out=sorted;
by test sid visit;
run;
proc transpose data=sorted out=trans;
by test sid;
var base;
id visit;
run;
data trans_new;
set trans;
if visit1^=. then baseline_v1=baseline;
if visit2^=. then baseline_v2=baseline;
run;
ods select summary;
ods output summary=summary;
proc means data=trans_new n mean std stackods;
class test;
var baseline baseline_v1 visit1 baseline_v2 visit2;
run;
ods output close;
ods select all;
data want;
set summary(where=(Variable='Baseline')) summary(where=(Variable^='Baseline'));
run;
If there are very much "visit", try a do loop based on array.
Since you ask to compute "Baseline (Vn)" who has corresponding subjects to the specific visit, I think you have to intro conditional computation. Here is an easy way:
proc sort data=have out=sorted;
by test sid visit;
run;
proc transpose data=sorted out=trans;
by test sid;
var base;
id visit;
run;
data trans_new;
set trans;
if visit1^=. then baseline_v1=baseline;
if visit2^=. then baseline_v2=baseline;
run;
ods select summary;
ods output summary=summary;
proc means data=trans_new n mean std stackods;
class test;
var baseline baseline_v1 visit1 baseline_v2 visit2;
run;
ods output close;
ods select all;
data want;
set summary(where=(Variable='Baseline')) summary(where=(Variable^='Baseline'));
run;
If there are very much "visit", try a do loop based on array.
It worked. Thanks.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.