topic Re: proc contents on last dataset by name and date in SAS Programming

proc contents on last dataset by name and date

Q1983 — Tue, 19 Apr 2022 22:19:51 GMT

Lets say I have a libname called myfiles with the contents as follows:

acct_cd_20210401

acct_cd_20210412

cof_fod_20200419

cof_fod_20220411

mrt_ftr_20200402

mrt_ftr_20220418

I want to do a proc contents on the last dataset in each category by name and date only (ie acct_cd_20210412,cof_fod_20220411,mrt_ftr_20220418)

proc contents data=myfiles. ????????

I want to get the names of fields in the tables however I do not want to have to do a proc contents on each and every table name. Can such a query be done?

Re: proc contents on last dataset by name and date

Reeza — Tue, 19 Apr 2022 22:33:15 GMT

Look at the SASHELP.VCOLUMN or dictionary.column table which contains the metadata for tables.

You can filter by the library easily. There is also a creation date but you'll need to add a column to separate your date into the date portion and prefix of the table name and then select only the records of interest.

@Q1983 wrote:

Lets say I have a libname called myfiles with the contents as follows:

acct_cd_20210401

acct_cd_20210412

cof_fod_20200419

cof_fod_20220411

mrt_ftr_20200402

mrt_ftr_20220418

I want to do a proc contents on the last dataset in each category by name and date only (ie acct_cd_20210412,cof_fod_20220411,mrt_ftr_20220418)

proc contents data=myfiles. ????????

I want to get the names of fields in the tables however I do not want to have to do a proc contents on each and every table name. Can such a query be done?

Re: proc contents on last dataset by name and date

ballardw — Tue, 19 Apr 2022 23:12:16 GMT

Last by which date? The date in the name? The creation date? The modified date?

Perhaps you might find this code of interest. Replace 'WORK' with the name of your Library in upper case. This creates a table with an X at the intersection of variable name and type and data set name. It is often useful to know that the same named variable occurs as both character and numeric. If you really want to you could add the other characteristics of variables such as Label, format or length but that may be disheartening if find lots of differences for same-named variables.

proc format;
value x
.=' '
other='X'
;
run;
proc tabulate data=sashelp.vcolumn;
   where libname='WORK' and memtype='DATA';
   class name memname type;
   table name*type,
         memname*n=' '*f=x.
         /misstext=' '
   ;
run;

Re: proc contents on last dataset by name and date

Tom — Tue, 19 Apr 2022 23:22:59 GMT

Get this list of MEMBERs in the library. Strip off the numeric suffix. Sort by the remaining text. Take the last (or first) one.

Let's test it. First let's make some datasets with those names.

data acct_cd_20210401 acct_cd_20210412 cof_fod_20200419 cof_fod_20220411 mrt_ftr_20200402 mrt_ftr_20220418;
  set sashelp.class(obs=1);
run;

Now make the ordered list.

proc sql ;
create table list as 
  select catx('.',libname,memname) as dsname
       , substr(memname,1,length(memname)-8) as basename
       , scan(memname,-1,'_') as suffix
  from dictionary.members 
  where libname='WORK'
    and not missing(input(scan(memname,-1,'_'),?yymmdd8.))
  order by 2,3
;
quit;

Now pick the last (latest) one for each basename and generate PROC CONTENT code.

data _null_;
  set list;
  by basename;
  if last.basename;
  call execute(catx(' ','proc contents data=',dsname,';run;'));
run;