SAS Coding

[Vicky1003]

Hi Guys, I hope everyone is doing well.

I need some help in building one code, I have a library with datasets from the last couple of years, I want to create a dataset with name of dataset present in the library and want to pick datasets which are present from last 30 days.

Requirement
Library XYZ
DATASET Present
ABC20200425
ABC20200424
..
..
..
ABC20190101

The dataset present in the library is from the start of 2019 to till date and the naming convention is the same as mentioned in the dataset present.
I want my output dataset to pick the last 30 days file only, it must be dynamic whatever date I will provide from there it should pick 30 days.
OUTPUT EXAMPLE
Suppose I passed date as 25th April in my code so the resulting output will be like

DATASET_NAME           MIN_DAY        MAX_DAY           MISSING_DATE
ABC                                20200325         20200425

Missing_Date column must have all the dates in  a comma-separated form  between 20200325 to 20200425 if any dataset is not available in the library between the min and max date

MIN_DAY is calculated on the basics of the day we pass in our code i.e. minimum day is 30 days less then the date what we pass in our code.

Any guidance on how should I approach will help me a lot. Thanks a lot

[Jagadishkatam]

Could you please try the below code where if you pass the date then it will give you the datasets names which were created in the last 30 days. In the libname pass the library name in which the datasets are located.

%macro test(date);
data want(where=(-30<=diff<=0));
set sashelp.vtable;
where libname='ADAM';
diff=intck('day',datepart(modate),&date)-30;
keep libname memname diff modate ;
run;
%mend;

%test(%nrstr(today()))

[Vicky1003]

Thanks for the help Jag, In my case, I have to take out date from the dataset name itself i.e.

ABC20200425, I have to extract the date from dataset name itself which will be (20200425), and the second part in which I need to look into the library and within 30 days range, I have to find out for what all dates there is no dataset present in the library. I am trying to make changes in your code and will update it over here. Thanks for looking.

Regards

Vicky

[Jagadishkatam]

Please try the below code, where we can use the memname from vtable to extract the date and use it

data want(where=(-30<=diff<=0));
set sashelp.vtable;
where libname='ADAM';
diff=intck('day',datepart(modate),input(compress(memname,,'kd'),yymmdd8.))-30;
keep libname memname diff modate ;
run;

[Kurt_Bremser]

Start with creating a template that has all required dataset names:

data needed;
length memname $3;
do date = today() - 30 to today();
  memname = "ABC" || put(date,yymmddn8.);
  output;
end;
keep memname;
run;

Then, join that with dictionary.tables to create the list of missing dates:

proc sql noprint;
select trim(substr(memname,4)) into :miss_list separated by ','
from needed a left join dictionary.tables b
on b.libname = "XYZ" and a.memname = b.memname
where b.memname is missing
;
quit;

Prepare and print output:

data result;
dataset_name = "ABC";
min_day = today() - 30;
max_day = today();
missing_date = "&missing_date";
format min_day max_day yymmddn8.;
run;

proc print data=result noobs;
run;

Untested, posted from my tablet.

[Vicky1003]

Thanks a ton, Kurt for looking into it, I am able to understand how first and third block of code is working just curious about the second block where you have used a dictionary. tables Just wanted to understand how using a "dictionary. tables" help was to go into a particular library and check for a dataset with a particular name. I am kind of new to dictionary. tables so my question might sound silly to you 🙂

Regards

Vicky

[Kurt_Bremser]

The dictionary tables contain metadata about the state of your current SAS session. They are available as tables only in SQL (in the pseudo library DICTIONARY), and as views (for use in proc and data steps) in SASHELP.

DICTIONARY.TABLES (SASHELP.VTABLE) contains information about all tables in all currently assigned libraries. Take a look at SASHELP.VTABLE to find out what's in there.

[Vicky1003]

Thanks a lot, Kurt now I am able to understand the dictionary table part and I guess your code will help me a lot to achieve what I need. I will try to create a macro were the user can pass any date, library, and dataset name to get information about the last thirty days.

Sorry for the late response, Thanks.

Thanks, everyone over here to help me out.

Regards

Vicky

[Vicky1003]

Hi Kurt,

Need one help, the second part of the code is not working fine for me as the library which I am trying to hit has another dataset than ABC, the dataset name can also start with another alphabet also. Though I am applying a filter of 30 days only but if my library has a dataset that goes outside the 30 days range I want to pick that dataset details and put it in the "Missing_date" list I tried it but it's not picking correctly.

The change I made in the middle part

proc sql noprint;
select trim(substr(memname,4)) into :miss_list separated by ','
from dictionary.tables a left join needed b
on a.libname = "XYZ" and a.memname = b.memname
where a.memname is missing and a.memname like "%ABC";
quit;

It gives me an error: Apparent invocation of macro ABC not resolved.

[Kurt_Bremser]

When you need to use a SQL "like" wildcard, enclose the string in single quotes, so that SAS does not attempt to resolve the apparent macro trigger.

[ChrisNZ]

If you want to do it in one pass:

%let date=%sysevalf('24APR2020'd);

data WANT;
   set ABC20: (obs=1)
       indsname= DSNAME
       end     = LASTOBS;
   length MISSING $800;
   retain MISSING   ' '
          MIN_DATE %sysfunc(intnx(day,&date,-30)) 
          MIN_DAY  %sysfunc(intnx(day,&date,-30),ymmddn8.) 
          MAX_DAY  %sysfunc(putn(&date,yymmddn8.)) ;
   if _N_=1 then do DT=MIN_DATE to &date;
     MISSING=catx(' ',MISSING,put(DT,yymmddn8.));
   end;
   MISSING=tranwrd(MISSING,compress(DSNAME,,'dk'), ' ');
   if LASTOBS then output;
run;