macro in libname

[SAS_INFO]

NOPE 😞

[ChrisNZ]

 OLDEST_ALLOWED=put(intnx('month',today(),-25),yymmn6.);

[SAS_INFO]

Hi Reez,

I tried your code,used libname concat, but it deleted only the first libname, location file, and rest dint get deleted.

libname A '/data/test1/a1/DATASET';

libname B '/data/test2/a2/DATASET';

libname C '/data/test3/a3/DATASET';

libname CONCAT (A B C );

%let table=;

data _null_;

OLDEST_ALLOWED='catt(year(today())-2,"_",month(today())-2)';

do until(symget('table')=' ');

RC=dosubl( 'data _null_;'

||'call symputx("table"," ");'

||'set SASHELP.VSTABLE; '

||'where LIBNAME="CONCAT" and '

||'trim(MEMNAME) like "%20____" and '

||'substr(MEMNAME, length(MEMNAME)-5,6) <' ||quote(OLDEST_ALLOWED)

||';call symputx("table",MEMNAME); stop; run;');

if lengthn(symget('table')) then

CR=dosubl( 'proc delete data=CONCAT.'||symget('table')||';run;');

end;

run;

[Reeza]

I did say your oldest_allowed was incorrect. Use Chris' version. 

Month returns a value between 1 and 12. If it's January your second term will edd up negative. 

[SAS_INFO]

hi Reeza,

I made the change in oldest_allowed. but still its not deleteing satasets.

Please Note, when I executed the code for the first time, it deleted the datasets.


libname A '/data/test1/a1/DATASET';
libname B '/data/test2/a2/DATASET';
libname C '/data/test3/a3/DATASET';

libname CONCAT (A B C );

%let table=;

data _null_;
OLDEST_ALLOWED=put(intnx('month',today(),-25),yymmn6.);

do until(symget('table')=' ');
RC=dosubl( 'data _null_;'
||'call symputx("table"," ");'
||'set SASHELP.VSTABLE; '
||'where LIBNAME="CONCAT" and '
||'trim(MEMNAME) like "%20____" and '
||'substr(MEMNAME, length(MEMNAME)-5,6) <' ||quote(OLDEST_ALLOWED)
||';call symputx("table",MEMNAME); stop; run;');
if lengthn(symget('table')) then
CR=dosubl( 'proc delete data=CONCAT.'||symget('table')||';run;');
end;
run;

[ChrisNZ]

This works.

Don't change things around unless you know what you are doing.

2 issues with your code:

1) you added an underscore, so the length of the date suffix is different

2) OLDEST_ALLOWED is badly constructed and fails for single-digit months.

option dlcreatedir source2;

libname A "%sysfunc(pathname(work))\a1";
libname B "%sysfunc(pathname(work))\a2";
libname C "%sysfunc(pathname(work))\a3";
libname CONCAT (A B C );

data A.A201201 
     B.A201201 
     C.A201201
     C.A201202;
  x=1;
run;

%let table=;
data _null_;
  OLDEST_ALLOWED=put(intnx('month',today(),-25),yymmn6.);
  do until(symget('table')=' ');
    RC=dosubl( 'data _null_;'
    ||'call symputx("table"," ");'
    ||'set SASHELP.VSTABLE; '
    ||'where LIBNAME="CONCAT" and '
    ||'trim(MEMNAME) like "%20____" and '
    ||'substr(MEMNAME, length(MEMNAME)-5,6) <' ||quote(trim(OLDEST_ALLOWED))
    ||';call symputx("table",MEMNAME); stop; run;');
    if lengthn(symget('table')) then
      CR=dosubl( 'proc delete data=CONCAT.'||symget('table')||';run;');
  end;
run;

[SAS_INFO]

hi,

for oldest allowed, I just have used what chris has mentioned

[SAS_INFO]

I hope u have read the code I have mentioned, I haven't added any underscore in oldest_allowed

[ChrisNZ · Accepted solution]

OK. I saw your second version, after

  OLDEST_ALLOWED='catt(year(today())-2,"_",month(today())-2)';

Anyway you can run my latest program as is and see that it does what you want.

Modify it to suit your needs.