topic Re: SET if exist in SAS Programming

SET if exist

Ronein — Mon, 15 Mar 2021 13:27:27 GMT

Hello

What is the way to fix the error in example 2.

I want to set data sets from a list and some of them are not existing.

May anyone show the code the fix the problem.

In real life I have many data sets between 2 dates and I want to set them (combine them).

The problem is that some of them are not existing,

/****example1-All data sets in SET statement exists****/
/****example1-All data sets in SET statement exists****/
/****example1-All data sets in SET statement exists****/
%let start=01032021;
%let end=04032021;

data tbl01032021;
input x y;
cards;
1 10
;
run;

data tbl02032021;
input x y;
cards;
2 11
;
run;

data tbl03032021;
input x y;
cards;
5 18
;
run;

data tbl04032021;
input x y;
cards;
6 16
;
run;

data _null_;
start_char=put(&start.,Z8.-L);
end_char=put(&end.,Z8.-L);
start_sas=mdy(substr(start_char,3,2),substr(start_char,1,2),substr(start_char,5,4));
end_sas=mdy(substr(end_char,3,2),substr(end_char,1,2),substr(end_char,5,4));
format start_sas end_sas  date9.;
counter = intck('day',start_sas,end_sas);
call symputx('n',put(counter,best.));
call symputx('start_sas',put(start_sas,best.));
call symputx('end_sas',put(end_sas,best.));

Run;
%put &n;
%put &start_sas;
%put &end_sas;

%macro months;
%do i=0 %to &n.;
m&i.=put(intnx('day',&start_sas.,&i.),ddmmyyn8.);
call symputx("m&i",trim(left(m&i.)));
%end;
%mend;

data series_days;
%months;
run;
%put &n;
%put &m0;
%PUT &&m&n..;

%macro sset; 
%do j=0 %to &n.;
tbl&&m&j..
%end;
%mend sset;
%put %sset;

Data wanted;
SET %sset;
Run;


/****example2-Set if exist ****/
/****example2-Set if exist ****/
/****example2-Set if exist ****/
%let start=01032021;
%let end=06032021;

data ttt01032021;
input x y;
cards;
1 10
;
run;

data ttt02032021;
input x y;
cards;
2 11
;
run;

data ttt03032021;
input x y;
cards;
5 18
;
run;

data ttt06032021;
input x y;
cards;
6 16
;
run;

data _null_;
start_char=put(&start.,Z8.-L);
end_char=put(&end.,Z8.-L);
start_sas=mdy(substr(start_char,3,2),substr(start_char,1,2),substr(start_char,5,4));
end_sas=mdy(substr(end_char,3,2),substr(end_char,1,2),substr(end_char,5,4));
format start_sas end_sas  date9.;
counter = intck('day',start_sas,end_sas);
call symputx('n',put(counter,best.));
call symputx('start_sas',put(start_sas,best.));
call symputx('end_sas',put(end_sas,best.));

Run;
%put &n;
%put &start_sas;
%put &end_sas;

%macro months;
%do i=0 %to &n.;
m&i.=put(intnx('day',&start_sas.,&i.),ddmmyyn8.);
call symputx("m&i",trim(left(m&i.)));
%end;
%mend;

data series_days;
%months;
run;
%put &n;
%put &m0;
%PUT &&m&n..;

%macro sset; 
%do j=0 %to &n.;
ttt&&m&j..
%end;
%mend sset;
%put %sset;

Data wanted;
SET %sset;
Run;

Re: SET if exist

Athenkosi — Mon, 15 Mar 2021 14:16:44 GMT

Try this.

%let start=01032021;
%let end=04032021;

proc sql noprint;
		select MEMNAME into :ds separated by ' ' from dictionary.tables
		where prxmatch('/tbl\d{8}/i',MEMNAME) and LIBNAME = 'WORK' 
	    and input("&start",MMDDYY8.)  <= input(compress(MEMNAME,,'a'),MMDDYY8.) <= input("&end",MMDDYY8.);
quit;

data all;
	set &ds;
run;

Re: SET if exist

Tom — Mon, 15 Mar 2021 14:50:28 GMT

It is hard to tell what your actual problem is. But if you want to check if a dataset exists then use the EXISTS() function.

Looks like your dataset names include a date component. If your issue is you want find all of the datasets that exist between two dates then just use a DO loop.

%let start=01032021;
%let end=04032021;

data _null_;
  start = input("&start",ddmmyy8.);
  end = input("&end",ddmmyy8.);
  format start end  date9.;
  counter=0;
  length dsname $41 ;
  do date=start to end ;
    dsname = cats('ttt',put(date,ddmmyyn8.));
    if exists(dsname) then do;
       counter+1;
       call symputx(cats('ds',counter),dsname);
    end;
  end;
  call symputx('counter',counter);
run;

data want;
  length dsname $41 ;
  set 
%do index=1 %to &counter ;
  &&ds&index
%end;
    indsname=dsname
  ;
  date = input(substr(dsname,length(dsname)-7),ddmmyy8.);
  format date date9.;
run;

Re: SET if exist

ballardw — Mon, 15 Mar 2021 14:42:57 GMT

I strongly suggest that you consider what might happen when you just let macro variables "fall into" you code like in this

%macro months;
%do i=0 %to &n.;
m&i.=put(intnx('day',&start_sas.,&i.),ddmmyyn8.);
call symputx("m&i",trim(left(m&i.)));
%end;
%mend;

You have two macro variables that are not defined with the macro. Which means you have no way of ensuring when you call the macro that a value exists or is the expected value.

Macros provide for parameters for a reason and including things your code expects to use will help you remember to provide the values, or defaults if they have not been set yet. It also helps control scope so one call to a macro doesn't accidentally change the value needed elsewhere.

Re: SET if exist

Ronein — Wed, 17 Mar 2021 05:45:35 GMT

Thank you.
As I understand the condition : prxmatch('/tbl\d{8}/i',MEMNAME)
is checking which data set name contain the substring "tbl" .
Can you explain please why did you write d{8}? (why not d{9} for example)