06-18-2014 05:17 PM
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 ;
I hard coded MAY14 as a demonstration. I need to be able to pull from a libname that has tables structured as follows:
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 ?
06-18-2014 07:07 PM
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 ;
%let refdate='01APR2014'd ;
%let dsn=NOT_FOUND ;
proc sql noprint ;
select memname into :dsn separated by ' '
( select memname
, input(scan(memname,2,'_'),MONYY5.) as month format monyy5.
and scan(memname,1,'_')= 'LINK'
and scan(memname,3,'_')= 'HE'
and input(scan(memname,2,'_'),MONYY5.) <= &refdate
having month = max(month)
%put dsn=&dsn ;
Need further help from the community? Please ask a new question.