SAS Error trying to loop through multiple datasets

Question

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

Allan Bowe · Accepted Answer · 2017-01-04 16:32:31Z

1

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;

edited Jan 4, 2017 at 16:32

answered Jan 4, 2017 at 15:56

Allan Bowe

12.8k20 gold badges83 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

DukeLuke Over a year ago

I'm new to macros. I always assumed you use macro functions inside of a macro, even if it's a datastep. So adding % sign in front. Thank you for this clarification :)

DukeLuke Over a year ago

Question : wouldn't the set statement concatenate the new note.file to work.new? I thought have different dataset in the set statement accomplished this removing the need for proc append? maybe it is overwriting it. I'm just curious how that works

Allan Bowe Over a year ago

Nope.. The macro language is a text generator, you use it to write (non macro) SAS code. For this reason, macro statements always run BEFORE the underlying data steps etc..

Allan Bowe Over a year ago

Yes you can - that's actually a better (more efficient) approach. To do so, you will need to define all of your libname statements in advance of running the data step. The set statement allows you to 'set' multiple input datasets, but - with the current syntax - you are rebuilding the output dataset (new) each time the step is run.

Allan Bowe Over a year ago

I've gone ahead and updated the the answer to incorporate the above.

|

Collectives™ on Stack Overflow

SAS Error trying to loop through multiple datasets

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related