code more efficient

[michtka]

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

[Ksharp · Accepted solution]

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]

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]

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]

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]

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]

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]

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]

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;