05-09-2018 08:45 AM
Hi all, I am trying now to set together several files, with this code:
%macro imp; %let mon = jan feb mar apr mai jun jul aug sep okt nov dez; data outtab; set %do jahr=16 %to 18; %do i=1 %to %sysfunc(countw(&mon)); %let next_mon = %scan(&mon, &i); work.to_&next_mon._&jahr. %end; %end; ; run; %mend;
It works fine. But it could be potentially a problem if one of the files doesn't exist. I want now to incorporate a corresponding check. So I made this change:
%macro imp; %let mon = jan feb mar apr mai jun jul aug sep okt nov dez; data outtab; set %do jahr=16 %to 18; %do i=1 %to %sysfunc(countw(&mon)); %let next_mon = %scan(&mon, &i); %if fileexist(to_&next_mon._&jahr.) %then work.to_&next_mon._&jahr.; %end; %end; ; run; %mend;
Unfortunately this doesn't work because %if fileexist is always FALSE although the files are available. I guess that %if fileexist cannot be placed in the data step. But what is the alternative way? Do You have some idea?
Thanks in advance.
05-09-2018 08:48 AM
Please see the FILEEXIST documentation
They give an example of using FILEEXIST inside of a macro involving the use of macro function %SYSFUNC
05-09-2018 08:49 AM
For macro language to use FILEEXIST, try wrapping it up inside %SYSFUNC:
%if %sysfunc(fileexist(to_&next_mon._&jahr.)) %then work.to_&next_mon._&jahr.;
05-09-2018 10:53 AM
Then I get
ERROR: Required operator not found in expression: fileexist(work.to_&next_mon._&jahr.)
FILEEXIST() is for physical filenames.
If you want to test if a dataset exists using a one or two level name then use the EXIST() function instead.
05-09-2018 08:57 AM
Make you life a lots easier (well less easy than not having like data in many different datasets in the first place) by having all your datasets either in one naming type, or in one libname. For instance one way using code generation:
data _null_; set sashelp.vtable (where=(libname="TMP")) end=last; if _n_=1 then call execute('data want; set'); call execute(' tmp.'||strip(memname)); if last then call execute('; run;'); run;
This will set every dataset in library tmp together - i.e. you don't need to do any calculation or checking the file exists. You can also use lists:
data want; set tmp.jan_: tmp.feb_: ...; run;
Note the colon after the prefix, means everything with a prefix jan_ for example.
Various methods of doing such a task, but should never really be needed as storing data out into small blocks with data (dates in this case) is no really a good storage method, very "Excel thinking".
05-09-2018 09:23 AM - edited 05-09-2018 09:23 AM
Thank You, it's a nice idea, but then I have to be sure that there are no other files in the library which is also not an optimal way for me.
05-09-2018 09:27 AM
Yes, unfortunately with a bad storage solution, you will end up with bad processes. Another way:
- set all data together, using indsname to create a date variable for filtering
- filter this dataset for the date ranges you want
05-09-2018 09:29 AM - edited 05-09-2018 09:31 AM
"Yes, unfortunately with a bad storage solution, you will end up with bad processes"
Yep. but I have no influence at all as for the storage solution. I am a very small person in a very large company.
Need further help from the community? Please ask a new question.