CODEWITHSUNDEEP
datesassyntax-error
DukeLuke
DukeLuke

Reputation: 315

SAS Error trying to loop through multiple datasets

I'm trying to run some code which will hopefully concatenate multiple months or years worth of data. I am trying to figure out when a field was populated with a value. I.e. there is field XYZ in my data set and it is populated with value A in November 2016. If I run my code from Jan - Dec I would like a new field populated with the date that SAS encounters a non-blank value in that field.

Here's my code:

    options mprint symbolgen source mlogic merror syntaxcheck ;

%macro append_monthly(iStart_date=, iEnd_date=);

  %local tmp_date i;
  %let tmp_date = %sysfunc(intnx(month,&iStart_date,0,beginning)) ;

  %do %while (&tmp_date le &iEnd_date);

    %let i = %sysfunc(sum(&tmp_date),yymmn4.);
    %put &i.;

    %let tmp_date = %sysfunc(intnx(month,&tmp_date,1,beginning)) ;

    libname note "my.qualifiers.fords.note&i." disp=shr;

data new ;
set note.file ;

%if ln_note_crbur_date_delinq ne '' %then spc_cmt_date = &i.;

run;

  %end;


%mend;
%append_monthly(iStart_date=%sysfunc(mdy(5,1,2016)),  iEnd_date=%sysfunc(mdy(10,1,2016)) );

LIBNAME _ALL_ CLEAR;

Here's a sample from log with errors :

SYMBOLGEN:  Macro variable TMP_DATE resolves to 20606
SYMBOLGEN:  Macro variable IEND_DATE resolves to 20728
MLOGIC(APPEND_MONTHLY):  %DO %WHILE(&tmp_date le &iEnd_date) condition is TRUE; loop will iterate again.
MLOGIC(APPEND_MONTHLY):  %LET (variable name is I)
SYMBOLGEN:  Macro variable TMP_DATE resolves to 20606
MLOGIC(APPEND_MONTHLY):  %PUT &i.
SYMBOLGEN:  Macro variable I resolves to 1606
1606
MLOGIC(APPEND_MONTHLY):  %LET (variable name is TMP_DATE)
SYMBOLGEN:  Macro variable TMP_DATE resolves to 20606
MPRINT(APPEND_MONTHLY):   spc_cmt_date = 1605 run;
SYMBOLGEN:  Macro variable I resolves to 1606
MPRINT(APPEND_MONTHLY):   libname note "my.qualifiers.fords.note1606" disp=shr;
ERROR: Unable to clear or re-assign the library NOTE because it is still in use.
ERROR: Error in the LIBNAME statement.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.NEW may be incomplete.  When this step was stopped there were 0 observations and 622 variables.
WARNING: Data set WORK.NEW was not replaced because this step was stopped.
NOTE: The DATA statement used 0.01 CPU seconds and 49483K.

NOTE: The address space has used a maximum of 4292K below the line and 240388K above the line.

I can't figure out why this isn't working. Maybe this could work using Proc append.

Basically, I just want my output with a field that returns a date in the form of YYMM for when field ln_note_crbur_date_delinq was non-blank.

Any help would be greatly appreciated

Upvotes: 0

Views: 303

Answers (1)

Allan Bowe
Allan Bowe

Reputation: 12691

I'd guess the reason for your error is that the handle is not being cleared on your source file before the next libname statement tries to re-assign.

An easy fix would be to use a different alias (libref) each time, as follows:

libname note&i "my.qualifiers.fords.note&i." disp=shr;

Then adjust your data step like so:

data new ;
  set note&i..file ;

The next part appears to be confusion between macro logic and data step. Simply remove the % symbols as follows:

if ln_note_crbur_date_delinq ne '' then spc_cmt_date = &i.;

Finally, add a proc append before the %end as follows:

proc append base=work.final data=new; run;

If work.final does not exist, it will be created in the same format as new.

EDIT:

following discussion in comments, here is a revised approach:

%macro append_monthly(iStart_date=, iEnd_date=);
  %local tmp_date i set_statement;
  %let tmp_date = %sysfunc(intnx(month,&iStart_date,0,beginning)) ;
  %do %while (&tmp_date le &iEnd_date);
    %let i = %sysfunc(sum(&tmp_date),yymmn4.);
    %let tmp_date = %sysfunc(intnx(month,&tmp_date,1,beginning)) ;
    %let set_statement=&set_statement &i..file;
    libname note&i "my.qualifiers.fords.note&i." disp=shr;
  %end;
  data new ;
    set &set_statement;
    if ln_note_crbur_date_delinq ne '' then spc_cmt_date = &i.;
  run;
%mend;
%append_monthly(iStart_date=%sysfunc(mdy(5,1,2016)),  iEnd_date=%sysfunc(mdy(10,1,2016)) );

LIBNAME _ALL_ CLEAR;

Upvotes: 1

Related Questions