Date:         Tue, 16 Dec 2008 20:55:41 -0800
Reply-To:     Patrick <patrick.matter@GMX.CH>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Patrick <patrick.matter@GMX.CH>
Organization: http://groups.google.com
Subject:      Re: Compare and read values into master data table
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

I missed the latest posts while playing around with an example. So
sorry for this a bit 'out-of-synch' post.

I think the following code below does more or less what sasbeginner
asked for.

For testing purposes do several runs with different values for
&startdate.

For 'productive' runs don't pass a value to &startdate.

HTH
Patrick

%let startdate='11feb2007'd;

/* List of Vars in source DS which need a Date-Suffix */
%let varlist= sales return;

/* load new monthly data */
%macro RenameVars;
  %if %symexist(startdate)=0 %then %let StartDate=%sysfunc(today());
  %else %if &StartDate= %then %let StartDate=%sysfunc(today());
  %let j=1;
  %do %while(%scan(&VarList,&j) ne );
    rename %scan(&VarList,&j)=%scan(&VarList,&j)%sysfunc( intnx
(month,&StartDate, -1, end),yymmn6. )%str(;)
    %let j=%eval(&j+1);
  %end;
%mend;
options mprint symbolgen;
data work.NewMonth;
  input Userid trans_id sales_code sales return;
  /*  set myora.myview; */
  %RenameVars
datalines;
1       100     10      50      10
30      104     10      10      5
4       105     12      5       2
9       105     12      5       2
;
options nomprint nosymbolgen;
proc sort data=work.NewMonth;
  by  userid trans_id sales_code;
run;


/**     add new monthly data to SalesMart master dataset   **/
/*  create appropriat varlist for run in current month      */
/*  keep only vars with Date-Suffix from the last 24 months */
%macro KeepList(months=,VarListStartDate=);
  %global KeepList KeepList_Comma;
  %let KeepList=;
  %if  &VarListStartDate= %then %let VarListStartDate=%sysfunc(today
());

  %do i=1 %to &months;
    %let j=1;
    %do %while(%scan(&VarList,&j) ne );
      %let KeepList=&KeepList %scan(&VarList,&j)%sysfunc( intnx
(month,&VarListStartDate, -&i, end),yymmn6. ) ;
      %let j=%eval(&j+1);
    %end;
  %end;

  %let  KeepList=%sysfunc( compbl(&KeepList) );
  %let  KeepList_Comma=%sysfunc( translate(&KeepList,',',' ') );
%mend;


/*%Varlist(months=24,ListName=KeepList24);*/
%KeepList(months=24);

/* ChKDS only needed if SalesMart doesn't exist already */
%macro ChKDS;
  %if %sysfunc(exist('WORK.SALESMART', 'DATA')) %then
  %do;
    data SalesMart(keep= userid trans_id sales_code &KeepList);
      attrib &KeepList length=8;
      set NewMonth;
      by userid trans_id sales_code;
    run;
  %end;
%mend;
%ChKDS

/* add NewMonth data to SalesMart  */
data SalesMart(keep= userid trans_id sales_code &KeepList);
  attrib &KeepList length=8;
  merge SalesMart NewMonth;
  by userid trans_id sales_code;
  /* delete 'empty' (outdated) records */
  if sum(&KeepList_Comma)=. then delete;
run;

proc print ;
run;