As the first step in the decommissioning of the site has been converted to read-only mode.

Here are some tips for How to share your SAS knowledge with your professional network.

Extracting transactions between two snapshots

From sasCommunity
Jump to: navigation, search

Duplicate page: Macro_Extract

Problem statement:

Given two data sets, with some time period between them

what are the differences?

  • adds == inserts
  • changes == updates
  • deletes == removes

--macro maven == the radical programmer 17:24, 3 April 2008 (EDT)

Note: you will need the Nobs Macro to run this program:

 /*       Name:
Requirements  : description  : given two data sets
                               identify differences:
                                        adds, changes, deletions
                purpose      : provide transactions to update database
returns from zero to maximum of three database transactions data sets
written to &OutLib.
names in &OutDataPrefix.(&ADD, &CHANGE, &DELETE.)
task: given data sets: BASE and DATA
      extract database transactions: Add   , Change, Delete
                                   : Insert, Update, Delete
Contexts      : program group: data processing
                program  type: routine
                SAS      type: macro with parameters
                uses routines: macro subroutine nobs
Specifications: input  : libref
  merge BASE, DATA
  by    &IDS.
  DATA, not in BASE: ADD
                output : data set(s)
Information   :  author: Ronald J. Fehd
assumptions: BASE and DATA sorted, no duplicates
NOTE: DELETES contains just IDS for database work
NOTE: CHANGES are brute force: complete observation is output,
              not just variable with new information
Usage Examples:
%Extract(InBase        = PREVIOUS
        ,InData        = CURRENT
        ,Ids           = IDLIST : data sorted, noduplicates
        ,OutDataPrefix = PREFIX : prefix of output EXTRACTion data sets
                               see SuffixAdd, SuffixChange, SuffixDelete
        ,InLib         = WORK   : optional, default is LIBRARY
        ,OutLib        = WORK   : optional, default is LIBRARY
/*** ................................... */
%Macro Extract/*required parameters:                                   */
(InBase    =  /*single level data set name                            */
,InData    =  /*single level data set name                            */
,Ids       =  /*list of identifiers                                   */
              /*optional parameters                                   */
,InLib     = Work   /*input and output                                */
,OutLib    = &InLib./*default to &LIBRARY.                            */
,OutDataPrefix =/*output data set prefix, see Suffix*                 */
,DataLabel = TRANSACT/*default data set label,                        **
                    identifies data set for later list processing     **
,OutDataSuffixes:                                                     */
,SuffixAdd    =ins /*suffix to &OutData                               */
,SuffixChange =upd /*suffix to &OutData                               */
,SuffixDelete =del /*suffix to &OutData                               */
,Testing      = 0  /*want TESTING messages printed?                   */
)/des = 'site: identify transactions'       /**/
 /*store  /*store & compile must have options mStored SASmStore=libref*/
;/*change notes
RJF2 02May17
RJF2 02May24 polishing, moved to mainframe
RJF2 02Jun10 added delete list of previous &Add, &Change, &Delete
RJF2 02Jun20 added outcomp and var to Compare
RJF2 02Jun25 changed merge Adds Deletes by, from &List. to &Ids.
RJF2 02Jun25 in %If OutLib ne WORK changed (Sort by &Ids.) to (Copy)
RJF2 06Sep27 polishing for SUG paper
RJF2 07Nov19 polishing for SMS reports usage: write to file PrintTo
RJF2 07Dec05 polishing for SMS reports usage:
     changed Nobs to function
     added data set &SysMacroName
*** ............................................................... */
%Let Testing = %eval(&Testing
               or %sysfunc(getoption(mprint)) eq MPRINT);
%local SqlObs SqlRc SqlLoops SqlXrc SqlXmsg; %*from PROC SQL usage;
%local NobsBase  ;%Let NobsBase  = 0;%*Nobs of transactions;
%local NobsData  ;%Let NobsData  = 0;%*Nobs of transactions;
%local Adds      ;%Let Adds      = 0;%*Nobs of transactions;
%local Changes   ;%Let Changes   = 0;%*Nobs of transactions;
%local ChangesNet;%Let ChangesNet= 0;%*Nobs of transactions;
%local Deletes   ;%Let Deletes   = 0;%*Nobs of transactions;
%local Matches   ;%Let Matches   = 0;%*Nobs of transactions;
%local List      ;%Let List      =  ;%*PROC Sql;
%Let OutLib  = %upcase(&OutLib.);%*resolve reference to &InLib.;
%If "&InBase." = ""  or "&InData." = "" or "&Ids."  = ""
    /*or "&OutDataPrefix."  = "" */
    %then %do;
    %Put er%str()ror: &SysMacroName. required parameter(s) missing;
    %Put InBase<&InBase.>InData<&InData.>Ids<&Ids.>;
    %GoTo Exit;
         %*delete data sets from previous work;
PROC SQL %if not &Testing. %then %do;
         noprint %end;;
         select  trim(upcase(MemName))
         into    :List  separated by ' '
         from    Dictionary.Tables
         where   LibName eq "&OutLib."
           and   MemName in (%upcase("&OutDataPrefix.&SuffixAdd."
%If &Testing. %then %Put note: &SysMacroName. List2Delete:<&List.>;
%If &SqlObs. %then               %do;
PROC DataSets library = &OutLib.
              memtype = data
              delete    &List.;
              quit;              %end;
      %*make Adds and Deletes;
DATA  &OutDataPrefix.&SuffixAdd.   (label = "&DataLabel.")
      &OutDataPrefix.&SuffixDelete.(label = "&DataLabel."
                                     keep  = &Ids.)
      &OutDataPrefix.Matched       (label = "&DataLabel.");
do    until(EndoFile);
merge &InLib..&InBase.(in = inBase)
      &InLib..&InData.(in = inData)
      end = EndoFile;
by    &Ids.;
if      not inBase and     inData then
                                  output &OutDataPrefix.&SuffixAdd.;
else if     inBase and not inData then
                                  output &OutDataPrefix.&SuffixDelete.;
else if     inBase and     inData then
                                  output &OutDataPrefix.Matched;
     end;%*do until(EndoFile);
             %*make CHANGES;
PROC Compare base     = &InLib..&InBase.
             compare  = &InLib..&InData.
             out      = &OutDataPrefix.&SuffixChange.
             /*?(keep    = &LIST.)?*/
             outcomp      %*write COMPARE data set obs                 ;
             outnoequal   %*write only obs with one or more differences;
             by         &Ids.     ;  %*kludge for data not sorted      ;
%Nobs(NobsBase,data = &InLib..&InBase.);
%Nobs(NobsData,data = &InLib..&InData.);
%Nobs(Adds    ,data = &OutDataPrefix.&SuffixAdd.   );
%Nobs(Changes ,data = &OutDataPrefix.&SuffixChange.);
%Nobs(Deletes ,data = &OutDataPrefix.&SuffixDelete.);
%Nobs(Matches ,data = &OutDataPrefix.Matched);run;
/*12/4/2007 4:11PM ************************************************/
%Let NobsBase = %Nobs(data = &InLib..&InBase.             ,missing = 0);
%Let NobsData = %Nobs(data = &InLib..&InData.             ,missing = 0);
%Let Adds     = %Nobs(data = &OutDataPrefix.&SuffixAdd.   ,missing = 0);
%Let Changes  = %Nobs(data = &OutDataPrefix.&SuffixChange.,missing = 0);
%Let Deletes  = %Nobs(data = &OutDataPrefix.&SuffixDelete.,missing = 0);
%Let Matches  = %Nobs(data = &OutDataPrefix.Matched       ,missing = 0);
%Let ChangesNet= %eval(&NobsBase. - &NobsData.);
%Put NobsBase  :&NobsBase. :: &InBase.;
%Put NobsData  :&NobsData. :: &InData.;
%Put Adds      :&Adds.;
%Put Changes   :&Changes.;
%Put Deletes   :&Deletes.;
%Put Matches   :&Matches.;
%Put ChangesNet:&ChangesNet. :: Base - Data;
%If &Changes. %then %do; %*add label;
PROC DataSets library = Work
              memtype = data nolist;
              modify    &OutDataPrefix.&SuffixChange.
              (label  = "&DataLabel.");
%If "&OutLib." ne "WORK"
    and (   &Adds.
         or &Changes.
         or &Deletes. )
    %then %do;
PROC Copy in       = Work
          out      = &OutLib.
          memtype  = data;
             %If &Adds.    %then &OutDataPrefix.&SuffixAdd.   ;
             %If &Changes. %then &OutDataPrefix.&SuffixChange.;
             %If &Deletes. %then &OutDataPrefix.&SuffixDelete.;
             %*select closure; ;
    %end;%*  .. %If &OutLib ne WORK;
%If &Testing.  %then %do;
    %If &Adds. %then %do;
        PROC Print data = &OutLib..&OutDataPrefix.&SuffixAdd.   ;
                   title2 "adds: &Adds.";
    %If &Changes. %then %do;
        PROC Print data = &OutLib..&OutDataPrefix.&SuffixChange.;
                   title2 "changes: &Changes.";
    %If &Deletes. %then %do;
        PROC Print data = &OutLib..&OutDataPrefix.&SuffixDelete.;
                   title2 "deletes: &Deletes.";
    run; title2;
*PROC PrintTo print = "ZqExtract-notes%sysfunc(datetime(),hex16.).txt"
DATA   &SysMacroName.;
attrib NameBase    length = $32 label = 'Name: Base'
       NameData    length = $32 label = 'Name: Data'
       NobsBase    length =   4 label = 'Nobs: Base'
       NobsData    length =   4 label = 'Nobs: Data'
       NobsAdds    length =   4 label = 'Nobs: Adds'
       NobsChanges length =   4 label = 'Nobs: Changes'
       NobsDeletes length =   4 label = 'Nobs: Deletes'
       ChangesNet  length =   4 label = 'Changes: Net'
%*       ChangesNobs length =   4 label = 'Changes: Nobs';
*file Print;
NameBase    = "&InBase.";
NameData    = "&InData.";
NobsBase    = &NobsBase.;
NobsData    = &NobsData.;
NobsAdds    = &Adds.;
NobsChanges = &Changes.;
NobsDeletes = &Deletes.;
ChangesNet  =  NobsData - NobsBase;
*todo: if want aligned then use;
*put @C1 'text' @C2 var;
put "base = &InLib..&InBase. nobs=&NobsBase.";
put "data = &InLib..&InData. nobs=&NobsData.";
*put "NobsChange: %eval(&NobsData. - &NobsBase.)" /;
put ChangeNobs=;
put "Adds = &Adds.";
put "Changes = &Changes.";
put "Deletes = &Deletes.";
put "NetChange: %eval(&Adds. - &Deletes.)";
put "Matches = &Matches.";
*PROC PrintTo;
%Exit: run;%* .......................... *; %Mend;
;/*\begin{test suite} ****************** ******************************
DATA   Previous
attrib IdN1  length =   4
       IdC1  length = $ 4
       IdN2  length =   4
       IdC2  length = $ 4
       Nmbr1 length =   4
       Char1 length = $ 4
%*enable one of the following loop control statements;
*do IdN1 = 1 to 10;%*Add, Change, Delete;
*do IdN1 = 1 to  8;%*Add, Change;
do IdN1 = 3 to  8;%*     Change;
*do IdN1 = 3 to 10;%*     Change, Delete;
                   IdC1  = put(IdN1,z4.);
                   Nmbr1 = IdN1* 17;
                   IdC2  = put(Nmbr1,z4.);
                   IdN2  = IdN1* 19;
                   Char1 = put(int(Nmbr1/13),4.);
                   *add 1,2;
   if IdN1 >= 3    then             output Previous;
                   *change 5,6;
   if IdN1 = 5     then Nmbr1=IdN1;
   if IdN1 = 6     then Char1='.';
                   *delete 9,10;
   if IdN1 <= 8    then             output Current;        %*do Id; end;
*PROC CONTENTS data = WORK.PREVIOUS;title2 previous;
*PROC PRINT    data = WORK.PREVIOUS;title2 previous;
*PROC PRINT    data = WORK.CURRENT ;title2 current;
%Let  Ids = IdN2;
%*LET  IDS = IdC1;
%*LET  IDS = IdC1 IdN2;
%Extract(InBase = Previous
        ,InData = Current
        ,Ids    = &IDS.
        ,OutDataPrefix = XYZ
        ,InLib  = Work
DATA  Test;
do    until(EndoFile);
merge Previous
      XYZdel  (in = isDelete)
      end = EndoFile;
by    &IDS.;
if    isDelete then delete;
else                output; end; stop; run;
PROC Compare base     = Current
             compare  = Test
             out      = Comp_Out
             outnoequal %*write only obs with one or more differences*;
             by         &Ids.;
%Nobs(     data = Comp_Out);%*correct if obs=0;
PROC Print data = Comp_Out;
%*test error message of missing parameter;
        ,Ids    = &Ids.
        ,OutDataPrefix = XYZ
        ,InLib  = WORK
%SymDel IDS;
run;/*\end{test suite}****************** ******************************/