DATA Step, Macro, Functions and more

Obtaining single summary report from DO LOOPS

Reply
Occasional Contributor
Posts: 17

Obtaining single summary report from DO LOOPS

Hi,

 

I have performed a minimization pro0cedure in several DO LOOPS and have got out estimates for each loop. Now, I want to obtain a single summary report for all the out estimates from all the DO LOOPS.  Can somebody guide me the procedure for it?

 

Valued Guide
Posts: 797

Re: Obtaining single summary report from DO LOOPS

Are you speaking of macro do loops?  in which proc's are run to generate your iterative estimates?  Or perhap regular do loops in a data step?

 

How about less mind reading from the volunteers on the forum and more information from you about the problem?  In particular the sas code by which you generate the iterative estimates you wish to summarize.  And also a clearer definition of what you mean by "summary report", even if what you mean is nothing more then a the sequence of estimates all in a single report.  (After all many optimization/minimization procedures report not only estimates, but also gradients, and other attributes of the problem structure).

 

 

Occasional Contributor
Posts: 17

Re: Obtaining single summary report from DO LOOPS

Hi,

 

Apologies for getting back late. I have attached my data file and the code is inserted below. I ran a macro do loop and created a rolling regression. My data genrated 36 rolling windowsand for each window, I got a set of parameter estimates. Now what I want is a single summary report for the 36 rolling windows with its parameter estimates, var, standard deviation, R square etc (maybe a kind of average results across the rolling windows).. It wou7ld be great if you can guide me through that.

 

proc import datafile="C:\Users\amalik\Desktop\STYLEDATA1.csv"
     out=return
     dbms=csv
     replace;
     getnames=yes;
run;

data return2;
set work.return;
FORMAT Date date9.;
run;

title ' data return2 ';
proc print;run;

* Macro with parameters;
%MACRO RW (data=  ,out_ds= ,model_equation= ,permno=  , date= ,STARTd=  ,ENDd= ,regprint=);

%DO i=1 %TO 72; /*8*12NUMBER OF 36th month rolling window */ 
                       /* all the dates available so 107 covers the whole period*/

 /* dm log 'clear';
        dm 'odsresults; clear';
*/

  %let Date = %Sysfunc( InputN( 01nov2007 ,Date9.)) ; /*to create a macro variable for reference date which is the beginning of the rolling window date*/
    %let dated = %Sysfunc( PutN( &date , Date9.)) ; /*to input date and give it format by putn*/
     %put Date = &Date ;
      %put Dated = &Dated;

%IF &i =1 %THEN %DO;
 
 %let START = %Sysfunc( InputN( 01nov2007, Date9. )) ; /*to create a macro variable for reference date which is the beginning of the rolling window date*/
    %let STARTd = %Sysfunc( PutN( &start , Date9.)) ; /*to input date by input and give it format by putn*/
     %put START = &START ;
      %put STARTd = &STARTd;

%let END= %Sysfunc( Intnx( Month , &start , 35,end )) ;
 %let  ENDd=%Sysfunc( PutN( &END , Date9.)) ;
  %put end   = &end;
  %put endd  = &endd;

%GOTO CONTINUE;/*TO SEND THE FIRST WINDOW(DATE) TO THE NEXT STEP WHICH IS SQL FOR SUBSETTING THE MAIN DATA*/

%END;

%Else %IF &i>1 %THEN %DO;
 
%let Start = %Sysfunc( Intnx( month, &Date ,&i-1)) ;/*to specify intervals with intnx ,start should be 1 month after the previouse starting date*/
 %let End = %Sysfunc( Intnx( Month , &start , 35,end)) ;/*monthly interval for end to specify 36 rolling window*/
  %put start = &start
        end = &end;

%let STARTd=%Sysfunc( PutN( &START , Date9.)) ; /*to get DATE9. FORMAT FOR date instead of default date*/ 
 %let  ENDd=%Sysfunc( PutN( &END , Date9.)) ;
  %PUT STARTd=&STARTd  
        ENDd=&ENDd;

%continue:

proc sql  NOPRINT; /* TO SUBSET OUR COMPLETE DATASET IN ACCORDANCE TO THE ROLLING WINDOWS INTERVALS*/
 create table temp1 as
 select*
 from work.return2
 where DATE BETWEEN "&STARTd"d and "&ENDd"d;
 quit;


/*1-2: RUNNING multiple regression/PER INTERVALS out EACH TIME*/*********************************************;

proc reg data=temp1 noprint outest=Out_Estimates ;
proc corr data=temp1 (drop=date)
   out=corrout(where=(_type_ ne 'CORR')) cov noprint;
run;

data stats(drop=_name_);
   set corrout;
   if _type_ = 'COV' then delete;
run;
proc transpose data=stats out=stats;
   id _type_;
run;

proc optmodel;
   /* declare parameters and read data */
   set <str> ASSETS;
   str target = 'AMP_AIT';
   set BENCH = ASSETS diff {target};
   num cov {ASSETS, ASSETS};
   read data stats into ASSETS=[_name_];
   read data corrout(where=(_type_='COV')) into [_name_]
      {i in ASSETS} <cov[_name_,i]=col(i)>;
/* let w1, w2, w3, w4 be the amount invested in each asset */
   var x{1..4} >= 0;
   /* declare optimization model */
   var W {BENCH} >= 0 <= 1;
   /* Var(X - Y) = Var(X) + Var(Y) - 2 Cov(X,Y) */
   min Variance = 
      sum {i in BENCH, j in BENCH} cov[i,j] * W[i] * W[j]
    + cov[target,target] 
    - 2 * sum {i in BENCH} cov[i,target] * W[i];
	/* subject to the following constraints */
    con weights:  sum {i in BENCH} W[i] = 1;

   /* call solver and print optimal solution */
   solve;
   print W;
quit;

%END; 

* Show INPUT data this regression: ;
title 'Temp1, loop ' &i ;
proc print data=temp1; RUN; 
title1 'Merged Results';
proc means data=temp1
n mean var std stderr;
var US_Small_Val US_Small_Gr US_Large_Gr _US_Large_Val;
run;


%END;
%MEND RW;


*Invoke the macro;
%RW (data= work.return2 ,
out_ds= work.temp1,
model_equation= AMP_AIT = US_Small_Val US_Small_Gr US_Large_Gr _US_Large_Val /noint ,
permno= 1 , date=date ,
STARTd= 01nov2007 ,
ENDd= 01sep2016,
regprint=noprint)
; QUIT;

Attachment
Occasional Contributor
Posts: 17

Re: Obtaining single summary report from DO LOOPS

Oh, just to correct myself. window size is 36 which generates 72 loops for my data not 36 loops.

Super User
Super User
Posts: 7,401

Re: Obtaining single summary report from DO LOOPS

Totally agree with @mkeintz, post test data, required output, code, logs etc so we can understand your question. 

 

A question from my side however, why are you doing loops over the same code/data?  SAS is built around the concept of by group processing.  So an example, I could do this:

 

%do i=1 to 2;
  proc print data=sashelp.class;
    where code=&i.;
    title "&i";
  run;
%end;

Or you could use built in by group processing:

proc print data=sashelp.class;
  by code;
  title "#byval1";
run;
Ask a Question
Discussion stats
  • 4 replies
  • 156 views
  • 1 like
  • 3 in conversation