Hello guys, anyone can help me to make this code more efficient(see code below), please not using sql:
I would like to do something with the week12, week28, week40 and week64,
try to do it more standard, because if I need to change the dataset new with different weeks (i.e week1, wek3, week5, week52),
I would have to change manually these weeks..and it is no really efficient.
something like week_i,where i=1 to 4
Any help?
Thanks in advance.
Please see the code below:
data new;
input visit &$6. treat &$7. pop :1. phase &$2.;
cards;
week12 placebo 1 I
week28 placebo 1 II
week40 drug 1 III
week64 drug 1 IV
week64 placebo 1 III
week40 placebo 1 III
week40 drug 1 I
week12 drug 1 II
week12 drug 1 II
week28 placebo 1 II
week28 placebo 1 II
week40 placebo 1 III
week40 placebo 1 IV
week12 drug 1 IV
week64 drug 1 III
week64 drug 1 II
week12 drug 1 I
week12 placebo 1 I
week64 placebo 1 II
week28 placebo 1 III
;
run;
*summary of subjects by phase;
proc summary data=new nway;
class treat phase visit;
var pop;
output out=newsum(drop=_:) n=n;
run;
*summary of subjects by treat;
proc summary data=new nway;
class treat;
var pop;
output out=new0sum (drop=_:) n=n2;
run;
*merging the summaries;
data newsumtotal;
merge new0sum newsum;
by treat;
run;
*transposing to get the final table;
proc transpose data=newsumtotal out=newsumtotalt;
by treat n2 phase;
id visit;
var n;
run;
*convert numeric variable to character (I want to make this part more efficient);
data newf (drop=week12 week28 week40 week64);
set newsumtotalt;
_week12=put(week12,3.0)||' '||'('||put(week12/n2,percent6.2)||')';
_week28=put(week28,3.0)||' '||'('||put(week28/n2,percent6.2)||')';
_week40=put(week40,3.0)||' '||'('||put(week40/n2,percent6.2)||')';
_week64=put(week64,3.0)||' '||'('||put(week64/n2,percent6.2)||')';
run;
data newfr (rename=(_week12=week12 _week28=week28 _week40=week40
_week64=week64) drop=_name_ );
retain treat n2 phase _week12 _week28
_week40 _week64;
set newf;
run;
V
How about:
data new; input visit &$6. treat &$7. pop :1. phase &$2.; cards; week12 placebo 1 I week28 placebo 1 II week40 drug 1 III week64 drug 1 IV week64 placebo 1 III week40 placebo 1 III week40 drug 1 I week12 drug 1 II week12 drug 1 II week28 placebo 1 II week28 placebo 1 II week40 placebo 1 III week40 placebo 1 IV week12 drug 1 IV week64 drug 1 III week64 drug 1 II week12 drug 1 I week12 placebo 1 I week64 placebo 1 II week28 placebo 1 III ; run; proc freq data=new noprint; tables treat/out=x(drop=percent) nocum nopercent; run; proc freq data=new noprint; tables treat*phase*visit/out=xx(drop=percent) nocum nopercent; run; data temp(drop=count); merge xx x(rename=(count=n2)); by treat; length value $ 20; value=catx(' ',count,cats('(',put(count/n2,percent8.2),')')); run; proc transpose data=temp out=want(drop=_name_); by treat n2 phase; id visit; var value; run; data name; set sashelp.vcolumn; where libname='WORK' and memname='WANT'; num=ifn(name eq: 'week',input(compress(name,,'kd'),best8.),0); run; proc sort data=name;by num;run; data _null_; set name end=last; if _n_ eq 1 then call execute('data want; retain '); call execute(name); if last then call execute(';set want;run;'); run;
Ksharp
How about:
data new; input visit &$6. treat &$7. pop :1. phase &$2.; cards; week12 placebo 1 I week28 placebo 1 II week40 drug 1 III week64 drug 1 IV week64 placebo 1 III week40 placebo 1 III week40 drug 1 I week12 drug 1 II week12 drug 1 II week28 placebo 1 II week28 placebo 1 II week40 placebo 1 III week40 placebo 1 IV week12 drug 1 IV week64 drug 1 III week64 drug 1 II week12 drug 1 I week12 placebo 1 I week64 placebo 1 II week28 placebo 1 III ; run; proc freq data=new noprint; tables treat/out=x(drop=percent) nocum nopercent; run; proc freq data=new noprint; tables treat*phase*visit/out=xx(drop=percent) nocum nopercent; run; data temp(drop=count); merge xx x(rename=(count=n2)); by treat; length value $ 20; value=catx(' ',count,cats('(',put(count/n2,percent8.2),')')); run; proc transpose data=temp out=want(drop=_name_); by treat n2 phase; id visit; var value; run; data name; set sashelp.vcolumn; where libname='WORK' and memname='WANT'; num=ifn(name eq: 'week',input(compress(name,,'kd'),best8.),0); run; proc sort data=name;by num;run; data _null_; set name end=last; if _n_ eq 1 then call execute('data want; retain '); call execute(name); if last then call execute(';set want;run;'); run;
Ksharp
Thanks Ksharp.
I couple of things:
1. You use proc freq intead of proc summary...it is another option as we talked. good.
2. Very nice the option value making the right format before transpose the varibales. Thank you. (I will get that the right answer).
3. Thank you for the last part, making the code more standard using the libraries of SAS, and using the function execute.
At the moment I dont need it because (from the transpose of visit, the variables weekX are generated, and I just got your solution in point 2, making the formatting before the transpose) Thanks!
One more wrinkle ... combine the first three steps into two, while eliminating a MERGE:
proc freq data=new noprint;
tables treat*phase*visit / out=xx(drop=percent);
run;
data temp (drop=count);
n2=0;
do until (last.treat);
set xx (keep=treat count);
by treat;
n2 + count;
end;
do until (last.treat);
set xx;
by treat;
length value $ 20;
value=catx(' ',count,cats('(',put(count/n2,percent8.2),')'));
output;
end;
run;
It should be faster ... hopefully you'll report back the results.
I don't know . I did not test it yet.
Maybe You are right. You are using a special skill DOW . Mine is CLASSIC method .
They both can get the same result.
Ksharp
Ksharp,
Actually, I wasn't asking you to do the testing. I was hoping the original poster would, as a sort of quid pro quo. Notice it's not just DOW, it's also using the output data set from PROC FREQ to calculate N2. There's no need to return to the original, larger data set for those calculations. Even sticking to your methods, you could have created XX first, then used XX to create X.
I'm reminded of the days when SAS first introduced PROC SQL. The reaction ranged widely. On the one hand, the SQL lovers were saying, "Great. I'll never need a DATA step again." And the SAS diehards were saying, "Why would I ever need that?" The best answer lies somewhere in between.
Astounding,
Ou. I understand what you mean. That is a good idea.
About the advantage of SQL and DataStep, it is hard to say. At some time, SQL is better ,whereas at some another time DataStep is better.
I recommend to master these two skill all, then you will find the world is so simple.
I heard it from somewhere that CLASSIC sas programmer is very arrogant and selfish.
But I don't think so , at least NOT for me. I LOVE SQL too.
Ksharp
Thanks Astounding for your help, I will keep your piece of code in my mind, as well as the execute function proposed by Ksharp (I dont need it at the moment).
Only just, I needed to be more "efficient" (I known is not the right word) format, as Ksharp suggested.
Thanks Ksharp, my final code with your help become like this (in boldface are the changes with my original code).
data new;
input visit &$6. treat &$7. pop :1. phase &$2.;
cards;
week12 placebo 1 I
week28 placebo 1 II
week40 drug 1 III
week64 drug 1 IV
week64 placebo 1 III
week40 placebo 1 III
week40 drug 1 I
week12 drug 1 II
week12 drug 1 II
week28 placebo 1 II
week28 placebo 1 II
week40 placebo 1 III
week40 placebo 1 IV
week12 drug 1 IV
week64 drug 1 III
week64 drug 1 II
week12 drug 1 I
week12 placebo 1 I
week64 placebo 1 II
week28 placebo 1 III
;
run;
*summary of subjects by phase;
proc summary data=new nway;
class treat phase visit;
var pop;
output out=newsum(drop=_:) n=n;
run;
*summary of subjects by treat;
proc summary data=new nway;
class treat;
var pop;
output out=new0sum (drop=_:) n=n2;
run;
*merging the summaries;
data newsumtotal;
merge new0sum newsum;
by treat;
length value $ 20;
value=catx(' ',n,cats('(',put(n/n2,percent8.2),')'));
run;
*transposing to get the final table;
proc transpose data=newsumtotal out=newsumtotalt;
by treat n2 phase;
id visit;
var value;
run;
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: