adding data to an existing dataset

Reply
Occasional Contributor
Posts: 10

adding data to an existing dataset

is there a way to add data to test to see if a data set exists and if it does add data to it?   I  get a monthly csv file FTP'd to a folder that i can and pull into a SAS dataset, and I'm wondering what the best approach is to be able to provide a 3 month rolling report.

For example - this is what I was thinking would work. 

Initially: create data set

Month1: of the report: add august to the dataset

Month 2: add September to the existing dataset

Month 3: add October

Month 4: Drop August and add November

Here is the code that I'm running, but when I run the second month the blank data set is recreated and i lose the first month.   What am I doing wrong with my "existing_file" test?   Thanks, JG

/*TEST TO SEE IF THE FILE EXISTS*/

data _null_;
dsn='nmg.rolling_3_month_data';
if(exist(dsn))then
     call symput('existing_file','Y');
else call symput('existing_file','N');
run;
%put &existing_file;

/*IF IT DOES NOT EXIST THEN CREATE A BLANK DATASET*/
data nmg.rolling_3_month_data;
if (&existing_file = 'N')then
  set raw_nmg_temp;
  drop &existing_file;
  stop;
run;

/*ADD THE DATA SETS*/
data nmg.rolling_3_month_data;
set nmg.rolling_3_month_data raw_nmg_temp;
run;

Super User
Posts: 19,851

Re: adding data to an existing dataset

Posted in reply to jgautreau

1. Create a master data set that has all the records.

2. Create a VIEW that has a where condition to pull the most three recent months in the dataset or based on the current date.

Respected Advisor
Posts: 4,173

Re: adding data to an existing dataset

Posted in reply to jgautreau

What I tend to do in such cases is to store the data in monthly data sets (eg: sample_201201, sample_201201,...) and I then just re-create a view pointing to the last n-months.

The advantage I see in such an approach is that I never have to care about deleting data in a master table when having to remove/archive data after a certain period of time (it's just moving/deleting SAS data files).
I'm also not getting a constantly growing table but kind of mimic what partitioning could do for me in a database. Creating such rolling views also makes it very easy to cover additional requirements like "we now also want a 6month view).

An alternative dealing with this single growing master table would be to store the data as SPDE data set. But you still would need to implement logic when it comes to removing old data.

And here some code illustrating the principle.

/* create monthly sample data sets */
%macro CreateSampleDs(StartDT, StopDT);
  %let startDT=%sysfunc(inputn(&StartDT,date9.));
  %let StopDT=%sysfunc(inputn(&StopDT,date9.));
  %let CurrDt=%sysfunc(intnx(month,&startDT,0,e));
  %do %while(%sysevalf(&CurrDt<&StopDT));
    data Sample_%sysfunc(putn(&CurrDt,yymmn6.));
      format date monyy7.;
      date=&CurrDt;
      do var=1 to 3;output;end;
    run;
    %let CurrDt=%sysfunc(intnx(month,&CurrDt,1,e));
  %end;

%mend;
%CreateSampleDs(01jan2012,01aug2012)


/* SQL creating a SQL view over the most current last n months */
%let CurrentNMonths=3;
proc sql noprint;
  select 'select * from '||cats(libname,'.',memname) into Smiley Frustratedampleview separated by ' outer union corr '
  from dictionary.tables
  where libname='WORK' and prxmatch('/\bSample_\d{6}\b/oi',memname)
  having intck('month',input(compress(memname,,'kd'),YYMMN6.)
                      ,max(input(compress(memname,,'kd'),YYMMN6.))
              )<= &CurrentNMonths -1
  ;

  create view Sample_Last&CurrentNMonths.Months as
    &Sampleview
  ;
quit;

%put &Sampleview;

Occasional Contributor
Posts: 10

Re: adding data to an existing dataset

Thanks - Very helpful.  Best, JG

Ask a Question
Discussion stats
  • 3 replies
  • 341 views
  • 6 likes
  • 3 in conversation