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

Hi,

 

I wrote a simple macro for Proc Phreg function below.

 

I would like to export the HR (95% C.I.) for all variables in one Excel file. Could anyone please help me with the code? Thank you all in advance. 

 

%macro HR(x=);
proc phreg data = CP1;
class &x;
model Time*Event(0) = &x /rl;
run;
%mend HR;

 

%HR(x=Gender);
%HR(x=Disease_Type);
%HR(x=Disease_Status);
%HR(x=Active_Therapy);

 

1 ACCEPTED SOLUTION

Accepted Solutions
Oligolas
Barite | Level 11

Hi,

I see, try this:

libname out xlsx "C:\want.xlsx";
%macro HR(x=);
   %let outputName=_PE%substr(%cmpres(&x.),1,%sysfunc(min(%length(&x.),29)));
   ods graphics off;
   ods output ParameterEstimates=&outputName.;

   proc phreg data = CP1;
      class &x;
      model Time*Event(0) = &x /rl;
   run;

   ods output close;
   ods graphics on;

%mend HR;

%HR(x=Gender);
%HR(x=Disease_Type);
%HR(x=Disease_Status);
%HR(x=Active_Therapy);


/**Ensure uniform variable length prior setting datasets**/
*Determine max variable lengths from metadata;
PROC SQL;
   CREATE TABLE temp AS
   SELECT DISTINCT * FROM
   (
      SELECT DISTINCT name,type,max(length) as length,varnum
      FROM sashelp.vcolumn
      WHERE LIBNAME eq 'WORK' and substr(memname,1,3) eq '_PE'
      GROUP BY name,type
   )
   ORDER BY varnum
   ;
QUIT;

*Generate attrib statement content. Uniform variable length - ensure no data is cut off;
DATA _NULL_;
   LENGTH attrib $ 2000;
   SET temp end=last;
   BY varnum;
   RETAIN attrib '';
   IF _N_ eq 1 THEN call missing(attrib);
   attrib=strip(attrib)||' '||strip(name);
   IF type eq 'char' THEN attrib=strip(attrib)||' length=$'||strip(put(length,best8.));
   ELSE attrib=strip(attrib)||' length='||strip(put(length,best8.));
   IF last THEN call symputx('gblattrib',attrib);
RUN;
%put &=gblattrib.;

*output data;
DATA out.ParameterEstimates;
   ATTRIB &gblattrib.;
   SET _PE:;
RUN;

*Clean up;
%SYMDEL gblattrib;
PROC DATASETS lib=work nolist; delete _PE: temp ParameterEstimates; RUN;QUIT;
libname out clear;

 

________________________

- Cheers -

View solution in original post

14 REPLIES 14
Oligolas
Barite | Level 11

Hi, use ODS Output:

 

libname out xlsx "C:\want.xlsx";
%macro HR(x=);
   %let outputName=_PE%substr(%cmpres(&x.),1,%sysfunc(min(%length(&x.),29)));
   ods graphics off;
   ods output ParameterEstimates=&outputName.;

   proc phreg data = CP1;
      class &x;
      model Time*Event(0) = &x /rl;
   run;

   ods output close;
   ods graphics on;

   data out.&outputName.;
   set &outputName.;
   run;
%mend HR;

%HR(x=Gender);
%HR(x=Disease_Type);
%HR(x=Disease_Status);
%HR(x=Active_Therapy);
________________________

- Cheers -

Denali
Quartz | Level 8

Hi @Oligolas ,

 

Thank you so much for the code! Could we optimize it and put all the HR(95%CI) in one single tab in the Excel? Maybe "append"  all the HR(95% CI)? At the moment, the code exported all the HRs and 95% CIs into seperate tabs by outcome (gender, active therapy).

 

Thanks again! 

Oligolas
Barite | Level 11

Hi,

I see, try this:

libname out xlsx "C:\want.xlsx";
%macro HR(x=);
   %let outputName=_PE%substr(%cmpres(&x.),1,%sysfunc(min(%length(&x.),29)));
   ods graphics off;
   ods output ParameterEstimates=&outputName.;

   proc phreg data = CP1;
      class &x;
      model Time*Event(0) = &x /rl;
   run;

   ods output close;
   ods graphics on;

%mend HR;

%HR(x=Gender);
%HR(x=Disease_Type);
%HR(x=Disease_Status);
%HR(x=Active_Therapy);


/**Ensure uniform variable length prior setting datasets**/
*Determine max variable lengths from metadata;
PROC SQL;
   CREATE TABLE temp AS
   SELECT DISTINCT * FROM
   (
      SELECT DISTINCT name,type,max(length) as length,varnum
      FROM sashelp.vcolumn
      WHERE LIBNAME eq 'WORK' and substr(memname,1,3) eq '_PE'
      GROUP BY name,type
   )
   ORDER BY varnum
   ;
QUIT;

*Generate attrib statement content. Uniform variable length - ensure no data is cut off;
DATA _NULL_;
   LENGTH attrib $ 2000;
   SET temp end=last;
   BY varnum;
   RETAIN attrib '';
   IF _N_ eq 1 THEN call missing(attrib);
   attrib=strip(attrib)||' '||strip(name);
   IF type eq 'char' THEN attrib=strip(attrib)||' length=$'||strip(put(length,best8.));
   ELSE attrib=strip(attrib)||' length='||strip(put(length,best8.));
   IF last THEN call symputx('gblattrib',attrib);
RUN;
%put &=gblattrib.;

*output data;
DATA out.ParameterEstimates;
   ATTRIB &gblattrib.;
   SET _PE:;
RUN;

*Clean up;
%SYMDEL gblattrib;
PROC DATASETS lib=work nolist; delete _PE: temp ParameterEstimates; RUN;QUIT;
libname out clear;

 

________________________

- Cheers -

haoduonge
Quartz | Level 8

Hi, Thank you so much for the code, saving quite some time!

However, when I use the option for reference group:

class &x (ref='first') - it shows error, I really appreciate if you could show where to change in the code lines for it.

Thanks!

Hao

Oligolas
Barite | Level 11

Hi,

 

well, I suspect you do not have a value of &x='first' in each of your &x variables.

You may need to determine the reference value for each of your variables.

 

I added such a dummy 'first' value in each of the variables in my test data and it worked, please find an example below.

data cp1;
   set sashelp.class;
   length Gender $5 Disease_Type $5 Disease_Status $8 Active_Therapy $5 Time 8 Event 8;
   Gender=sex;
   Disease_Type=ifc(sex eq 'M','One','Two');
   Disease_Status=ifc(sex eq 'M','Resolved','Ongoing');
   Active_Therapy=ifc(sex eq 'M','Y','N');
   Time=age;
   Event=mod(age,2);

   *add a reference to all variables;
   array vars _CHARACTER_;
   do over vars;
      if mod(_N_,3) eq 0 then vars='first';
   end;
   keep Gender Disease_Type Disease_Status Active_Therapy Time Event;
run;

 

________________________

- Cheers -

haoduonge
Quartz | Level 8

I tried your syntax, adding it in the data set before running all - wonder if this is correct but results came out the same with last value as reference not the first one. Do we need to adjust anything in 'data _NULL'?

Thanks!

Oligolas
Barite | Level 11

hi,

 

you need to check for each of your variable what the reference should be. I added it for example purposes only. Consult a statistician to determine the reference.

________________________

- Cheers -

Denali
Quartz | Level 8
Bravo! It worked! THANK YOU SO MUCH!!!
haoduonge
Quartz | Level 8

Hi Oligolas,

I know this topic was solved but my question is relevant to it, so I ask it here.

Your codes work great for proc phreg with time invarying covariates.

Can you help with codes for modeling multiple time varying covariates as below?

I have about 15 sets of variables (X, Y....) so it would be great not to repeat them 15 times.

Thank you!

 

proc phreg data=table1;

model TIME*outcome (0)=X/TIES = EFRON RL;

   array pp{*} X1-X5;

   array tt{*} t1-t6;   t1=1;  t2=2;  t3=3;  t4=4;  t5=5;   t6=6

   if TIME <tt[1]       then X=X0; 

   else if TIME >= tt[6then X=X6;

   else do i=1 to dim(pp); if tt[i] <= TIME < tt[i+1then X= pp[i];end;

run;

 

Oligolas
Barite | Level 11

Hi,

not sure I understood you properly, you mean, you'd like to do the same proc phreg for the variable Y and so on like this?

proc phreg data=table1;
   model TIME*outcome (0)=Y/TIES = EFRON RL;
   array pp{*} Y1-Y5;
   array tt{*} t1-t6;   t1=1;  t2=2;  t3=3;  t4=4;  t5=5;   t6=6; 
   if TIME <tt[1]       then Y=Y0; 
   else if TIME >= tt[6] then Y=Y6;
   else do i=1 to dim(pp); if tt[i] <= TIME < tt[i+1] then Y= pp[i];end;
run;

if yes then you only need to loop over the variables that you need:

%MACRO loop(varlist=x y z);
   %local varlist i currItem;
   %LET i=1;
   %LET currItem=%SCAN(&varlist.,&i.,%STR( ));
   %DO %WHILE(%LENGTH(&currItem.)>0);


      proc phreg data=table1;
         model TIME*outcome (0)=&currItem./TIES = EFRON RL;

         array pp{*} &currItem.1-&currItem.5;
         array tt{*} t1-t6;   t1=1;  t2=2;  t3=3;  t4=4;  t5=5;   t6=6; 
         if TIME <tt[1]       then &currItem.=&currItem.0; 
         else if TIME >= tt[6] then &currItem.=&currItem.6;
         else do i=1 to dim(pp); if tt[i] <= TIME < tt[i+1] then &currItem.= pp[i];end;
      run;

      %put &=varlist &=i &=currItem;
      %LET i=%EVAL(&i.+1);
      %LET currItem=%SCAN(&varlist.,&i.,%STR( ));
   %END;
%MEND loop;
%loop(varlist=X Y Z);
________________________

- Cheers -

haoduonge
Quartz | Level 8

Yes, wonderful!

Thanks so much!

Hao

blueskyxyz
Lapis Lazuli | Level 10

Wen_23_0-1642065744413.png

ods output ParameterEstimates= Est;
proc phreg data=have;
model AVAL*y(1)=sexn AGEGR1N/ALPHA=0.05 rl;
run;

 

hi , try the option -- ALPHA=0.05 rl

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 14 replies
  • 3330 views
  • 2 likes
  • 4 in conversation