Macro Last12 with link and return

From sasCommunity
Jump to: navigation, search

2013-March-24: deprecated by the Author: Ronald_J._Fehd macro maven

This function can be done with Macro DateLoop.

%let today    = %sysfunc(today());
%let interval = month;
%let begin    = %sysfunc(intnx(&interval,&Date,-12,begin));
%let begin    = %sysfunc(intnx(&interval,&Date,- 1,end  ));
 
%dateloop(loop_start= &begin
         ,loop_stop = &end
         ,interval  = month
         ,MacroText = %nrstr(libref.ABC_&ccYY._&MM._01)
         )

Program RnD-last-12

 /*RnD-last-12;
description: a macro function which generates a set of tokens
             in this case a set of Libref.DataName
             for the last 12 months of a set of snapshots
purpose    : demonstration of macro implementation
             of data step link+return
********/
%put SysDate=&SysDate.;
%put SysDate9=&SysDate9.;
%Let Year_curr = 2012;
%Let Year_curr = %substr(&SysDate9.,%length(&SysDate9.)-3,4);
%Let Year_curr = %sysfunc(year("&SysDate."d));
%Let Year_prev = %eval(&Year_Curr. -1);
%Let MM_curr = 3;
%Let MM_curr = %sysfunc(month("&SysDate."d));
%Let MM_prev = %eval(&MM_Curr. -1);
%Put _user_;
*endSAS;
 
%Macro last12
      (year  = %sysfunc( year("&SysDate."d))
      ,mm_lo = 1
      ,mm_hi = %sysfunc(month("&SysDate."d))
      );
%local ccYY I MM;      
%if     &MM_Lo. eq 1 
    and &MM_Hi. eq 12 %then 
    %do I = &MM_Lo. %to &MM_Hi.;
        %if %length(&I) eq 1 %then %let MM = 0&I.;
        %else                      %let MM =  &I.; 
        %let Return_to = Return_1_12;
        %goto Text;
        %Return_1_12:
        %end;
%else   %do;
    %let ccYY = %eval(&Year. - 1);
    %*for last13 this is:;
    %*do I = %eval(&MM_Hi.   ) %to 12;
    %do I = %eval(&MM_Hi. + 1) %to 12;
        %if %length(&I) eq 1 %then %let MM = 0&I.;
        %else                      %let MM =  &I.; 
        %let Return_to = Return_N_12;
        %goto Text;
        %Return_N_12:
        %end;
    %let ccYY = &Year;
    %do I = 1 %to &MM_Hi.;
        %if %length(&I) eq 1 %then %let MM = 0&I.;
        %else                      %let MM =  &I.; 
        %let Return_to = Return_1_hi;
        %goto Text;
        %Return_1_hi:
        %end;
    %end;
 
%goto Skip_This;
 
%Text: Library.GAL_&ccYY._&MM._01(in = Have_&MM.)
       %goto &Return_to.;
 
%Skip_This:    
%mend;
 
%Put note: default
%last12;  
 
*endSAS;
 
%Put note: &Year_prev.-&MM_prev. 
%last12
      (year  = &Year_prev.
      ,mm_hi = &MM_Prev.
      );
 
%Put note:
%last12
      (year  = &Year_curr.
      ,mm_hi =   &MM_curr.
      );

SAS-L reframe by Søren Lassen

http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1203B&L=sas-l&P=R1971

%macro last12
      (year  = %sysfunc(date(),year4.),
       mm_hi = %sysfunc(month(%sysfunc(date())))
       );
%local m text;
%let text=
 %nrstr(Library.GAL_&year._%substr(&m,2)_01(in=Have_%substr(&m,2)));
%if &mm_hi=12 %then %let mm_hi=0;
%else              %let year=%eval(&year-1);
%do m=101+&mm_hi %to 112; 
    %unquote(&text)
    %end;
%let year=%eval(&year+1);
%do m=101 %to 100+&mm_hi; 
    %unquote(&text)
    %end;
%mend;

Bells and Whistles

 /* name: ...\SAS-site\macros\prev12.sas
description: function returns text with references to ccYY MM YY or Mon
purpose    : provide method to generate series of text inside
             data set names, variable attribute, array references
             if statements
author: Ronald J. Fehd  2012-Mar-20
used in
Z:\GAL\sas\06-make-prev13.sas
Z:\SAS-site\includes\snapshot-accum-smry-attrib.sas
Z:\SCCM-SAS\sas-1221-monthly-job-times\03-snapshot-accum-smry-attrib-create.sas
text=%nrstr(Library.GAL_&ccYY._&MM_01(in=Have_&ccYY._&MM.))
array M(12) %prev12(text=%nrstr(m&MM.));
50        +     array M(12) %prev12(text=%nrstr(m&MM.));
MACROGEN(PREV12):   m03 m04 m05 m06 m07 m08 m09 m10 m11 m12 m01 m02
%Put note:     array M(12) %prev12(text=%nrstr(m&Mon.));
51        +%Put note:     array M(12) %prev12(text=%nrstr(m&Mon.));
note: array M(12) mMar mApr mMay mJun mJul mAug mSep mOct mNov mDec
                  mJan mFeb
%Put note: %prev12(text=%nrstr(m&MM length = 8 label = "&ccYY. &Mon."));
52 +%Put note: %prev12(text=%nrstr(m&MM length = 8 label = "&ccYY. &Mon."));
note: m03 length = 8 label = "2011 Mar"
      m04 length = 8 label = "2011 Apr"
      ...
      m12 length = 8 label = "2011 Dec"
      m01 length = 8 label = "2012 Jan"
      m02 length = 8 label = "2012 Feb"
************ */
%macro prev12
       (ccYY  =                %sysfunc(date(),year4.)
       ,mm_hi = %sysfunc(month(%sysfunc(date()))) -1
       ,text=
       );
%local I MM Mon YY;
%if &mm_hi = 12 %then %let mm_hi = 0;
%else                 %let ccYY  = %eval(&ccYY.-1);
%let YY = %substr(&ccYY.,3,2);
%do I = 101+&mm_hi. %to 112;
    %let MM  = %substr(&I,2);
    %let Mon = %sysfunc(putn(%sysfunc(mdy(&MM.,1,&ccYY.)),MonName3.));
    %unquote(&Text)
    %end;
%let ccYY =   %eval(&ccYY+1);
%let YY   = %substr(&ccYY.,3,2);
%do I = 101 %to 100+&mm_hi;
    %let MM  = %substr(&I,2);
    %let Mon = %sysfunc(putn(%sysfunc(mdy(&MM.,1,&ccYY.)),MonName3.));
    %unquote(&Text)
    %end;
%mend;

Programs

Provide 13 Snapshots in One Data Set

;/*    name: 06-make-prev13
description: provide last 13 snapshots of GAL to other projects
purpose    : standardize look-ups
;/*********/
%Let In_Lib  = Library;
%Let Out_Lib = LibSite;
*Let Out_Lib = Work;
 
%Let Out_Data = GAL_prev13;
options mprint;
 
%macro prev13
       (year  = %sysfunc(date(),year4.)
       ,mm_hi = %sysfunc(month(%sysfunc(date())))
       ,text=
%nrstr(Library.GAL_&year._%substr(&m,2)_01(in=Have_&year._%substr(&m,2)))
       );
%local m text;
%if &mm_hi=12 %then %let mm_hi=0;
%else    %let year=%eval(&year-1);
%*NOTE DECREMENT  -1 THIS RETURNS 13 snapshots;
%do m=101+&mm_hi. -1 %to 112;
    %unquote(&text)
    %end;
%let year=%eval(&year+1);
%do m=101 %to 100+&mm_hi;
    %unquote(&text)
    %end;
%mend;
 
DATA &Out_Lib..&Out_Data.(drop = CoCenter Var15);
     if 0 then do;
        set Library.GAL_&DateSAS.(keep = UserId);
        attrib date_GAL length = 8 label = 'date GAL'
                        format = yymm.;
        set Library.GAL_&DateSAS.(drop = UserId);
        end;
 
do until(EndoFile);
   set %prev13
       end = EndoFile;
   by UserId;
 
%prev13(text=
%nrstr(if Have_&year._%substr(&m,2) then date_GAL = mdy(%substr(&m,2),1,&Year.);)
)
   if last.UserId then output;
   end;
stop;
 
PROC SQL; describe table &SysLast.;
          quit;
run;

Make Data Structure of Yearly Report

 /* name: Z:\SAS-site\includes\snapshot-accum-create.sas
description: create the accumulating snapshot
purpose    : standardize snapshot variable names
             periodic: &Software.-Jobs -Minutes -Months
             accumulating; jobs* months*
usage:
%Let Out_Lib  = Work;
%Let Out_Lib  = Library;
%Include SiteIncl(snapshot-accum-smry-attrib);
RJF2 3/19/2012 added comments for array of ?01--?12
*****/
 
%Let Out_Base = &Software._&SCCM._accum;
 
DATA &Out_Lib..&Out_Base.;
     attrib UserId             length = $4
            &Software._Jobs    length =  8
            &Software._Minutes length =  8
            &Software._Months  length =  8
 
%prev12(text=%nrstr(j%substr(&m,2) length = 8))
%prev12(text=%nrstr(m%substr(&m,2) length = 8))
 
/**replaces: *******************************
            j01         length = 8
            j02         length = 8
            j03         length = 8
            j04         length = 8
            j05         length = 8
            j06         length = 8
            j07         length = 8
            j08         length = 8
            j09         length = 8
            j10         length = 8
            j11         length = 8
            j12         length = 8
 
            m01         length = 8
            m02         length = 8
            m03         length = 8
            m04         length = 8
            m05         length = 8
            m06         length = 8
            m07         length = 8
            m08         length = 8
            m09         length = 8
            m10         length = 8
            m11         length = 8
            m12         length = 8
/*********************************/
            ;
stop;
 
PROC SQL; describe table &SysLast.;
          quit;

Building Both Accumulating and Periodic Snapshot

 /* name: Z:\SAS-site\includes\snapshot-accum-smry-update.sas
description: update the accumulating snapshot
purpose    : standardize snapshot variable names
                         and calculations
             periodic: &Software.-Jobs -Minutes -Months
             accumulating; jobs* months*
usage:
autoexec: %Let Software = SAS;*JMP;
 
%Let Out_Lib  = Work;
%Let Out_Lib  = Library;
%Include SiteIncl(snapshot-accum-smry-update);
RJF2 3/19/2012 added comment of array ?01--?12
***/
%Let Out_Lib  = Work;
%Let Out_Lib  = Library;
 
%Let In_Base  = &Software._&SCCM._accum;
%Let In_Data  = &Software._&SCCM._UserIds_&DateSAS.;
%Let Out_Data = &Software._&SCCM._accum_smry;
%Let _MM = %substr(&DateSAS,%length(&DateSAS)-1,2);
%Put note: MM=&_MM.;
 
DATA &Out_Lib..&Out_Data.;
     if 0 then set Library.&In_Base.;
 
    *sliced bread: dynamic for any ending month;
     array J(12) %prev12(text=%nrstr(j&MM.));
     array M(12) %prev12(text=%nrstr(m&MM.));
 
    *terminal cuteness: ;
    *array J(12) j04--j12 j01 j02 j03;
    *array M(12) m04--m12 m01 m02 m03;
    *mundane: ;
    *array J(12) j01--j12;
    *array M(12) m01--m12;
 
do until(EndoFile);
   merge Library.&Out_Data.
         Library.&In_Data  (drop = MM
                            rename = (Jobs    = J&_MM.
                                      Minutes = M&_MM.
                           )         )
         end = EndoFile;
   by    UserId;
   &Software._Jobs    = sum(of J(*));
   &Software._Minutes = sum(of M(*));
   &Software._Months  =   n(of J(*));
   output;
   end;
stop;
run;

References

-- created by User:Rjf2 8 March 2012 (EST)

--Ronald_J._Fehd macro maven