BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Q1983
Lapis Lazuli | Level 10

%macro get_current(mondt);

proc sql;

create table hands as

select a.*, b.src_sys_acct_Key 

from combine1 a  

left join libname.Link_&mondt_he b on input(ln_no, 10.) = input(b.loan_num, 10.)

where src_sys_acct_key is not null ;

quit;

%mend;

%get_current (MAY14);

I hard coded MAY14 as a demonstration.  I need to be able to pull from a libname that has tables structured as follows:

link_MAY14_he

link_APR14_he

link_MAR14_he

Since this is June, the most recent month available would be MAY14.  Alternatively if there is a way to get the system date one month ago in MMYY format and then use in a macro ?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

%let date=%sysfunc(intnx(month, %sysfunc(today()), -1), monyy5.);

%put &date.;

View solution in original post

2 REPLIES 2
Reeza
Super User

%let date=%sysfunc(intnx(month, %sysfunc(today()), -1), monyy5.);

%put &date.;

Tom
Super User Tom
Super User

Use INTNX() function to generate a relative date.  You can apply MONYY5. format to convert it to MONyy format.

But perhaps what you want to do is find the file with the latest name?

You can query the list of table names from the metadata if you want and parse the table name into the pieces so that you can convert the middle of the name back to a date that you can then compare to any reference date, not just today.

* Make some example tables ;

data link_MAY14_he link_APR14_he link_MAR14_he ;

run;

%let refdate='01APR2014'd ;

%let dsn=NOT_FOUND ;

proc sql noprint ;

  select memname into :dsn separated by ' '

  from

select memname

        , input(scan(memname,2,'_'),MONYY5.) as month format monyy5.

  from dictionary.members

  where libname='WORK'

    and scan(memname,1,'_')= 'LINK'

    and scan(memname,3,'_')= 'HE'

    and input(scan(memname,2,'_'),MONYY5.) <= &refdate

)

  having month = max(month)

  ;

quit;

%put dsn=&dsn ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 8591 views
  • 3 likes
  • 3 in conversation