BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
michtka
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

8 REPLIES 8
Ksharp
Super User

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

michtka
Fluorite | Level 6

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!


Astounding
PROC Star

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.

Ksharp
Super User

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

Astounding
PROC Star

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.

Ksharp
Super User

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

michtka
Fluorite | Level 6

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.

michtka
Fluorite | Level 6

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;

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!

What is Bayesian Analysis?

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.

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
  • 8 replies
  • 1198 views
  • 4 likes
  • 3 in conversation