BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SAS_INFO
Quartz | Level 8
NOPE 😞
ChrisNZ
Tourmaline | Level 20

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

SAS_INFO
Quartz | Level 8

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
Super User

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
Quartz | Level 8
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
Tourmaline | Level 20

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
Quartz | Level 8
hi,

for oldest allowed, I just have used what chris has mentioned
SAS_INFO
Quartz | Level 8
I hope u have read the code I have mentioned, I haven't added any underscore in oldest_allowed
ChrisNZ
Tourmaline | Level 20

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.

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 23 replies
  • 4330 views
  • 3 likes
  • 4 in conversation