DATA Step, Macro, Functions and more

Do same steps on 100 files, all files in a particular library

Accepted Solution Solved
Reply
Regular Contributor
Posts: 225
Accepted Solution

Do same steps on 100 files, all files in a particular library

Please see code below:

 

proc sort data=have_100;
by variable_rank variable_continuous;
run;

data have_101_divisions;
set have_101;
by variable_rank;
if last.variable_rank;
run;

The actual permanent data library being worked in has 100 such files:

 

have_100

have_101

have_102

have_103

.

.

.

have_199

 

Is there a way to avoid copy-paste 100 times, changing the specifics each time.

 

Additional motivation is that sometimes the number of files, and file names, changes.  For instance, 150 files in the folder.  And named, have_200, have_345, etc.

 

I'll be doing the above many different times in the future.

 

Suggestions greatly appreciated.

 

Nicholas Kormanik

 

 


Accepted Solutions
Solution
‎09-13-2017 12:46 AM
Frequent Contributor
Posts: 129

Re: Do same steps on 100 files, all files in a particular library

[ Edited ]
Posted in reply to NicholasKormanik

Yes there is:

data _NULL_;
   set sashelp.vtable;
   where libname eq upcase('YourLib');
   call execute('
      PROC SORT data='||strip(libname)||'.'||strip(memname)||' out=tmp;
         BY variable_rank variable_continuous;
      RUN;

      DATA yourPermLib.'||strip(memname)||'_divisions;
         SET tmp;
         BY variable_rank;
         if last.variable_rank;
      RUN;

      PROC DATASETS lib=work noprint;
         delete tmp;
      RUN;QUIT;');
run;

 

________________________

- Cheers -

View solution in original post


All Replies
Solution
‎09-13-2017 12:46 AM
Frequent Contributor
Posts: 129

Re: Do same steps on 100 files, all files in a particular library

[ Edited ]
Posted in reply to NicholasKormanik

Yes there is:

data _NULL_;
   set sashelp.vtable;
   where libname eq upcase('YourLib');
   call execute('
      PROC SORT data='||strip(libname)||'.'||strip(memname)||' out=tmp;
         BY variable_rank variable_continuous;
      RUN;

      DATA yourPermLib.'||strip(memname)||'_divisions;
         SET tmp;
         BY variable_rank;
         if last.variable_rank;
      RUN;

      PROC DATASETS lib=work noprint;
         delete tmp;
      RUN;QUIT;');
run;

 

________________________

- Cheers -

Regular Contributor
Posts: 225

Re: Do same steps on 100 files, all files in a particular library

Beautiful, Oligolas.  Thank you very much.

 

Two issues:

 

1. I see all the resultant created files using the 'Explorer' in the 'Work' library.  Need to put these into my permanent library.

 

2. The error message appears:

"NOTE: The file SAS_1.TMP (memtype=DATA) was not found, but appears on a DELETE statement."

I see TMP in the 'Work' library.  Tried "delete WORK.tmp," but didn't fix issue.

 

Below is the actual code I ran:

 

data _NULL_;
   set sashelp.vtable;
   where libname eq upcase('SAS_1');
   call execute('
      PROC SORT data='||strip(libname)||'.'||strip(memname)||' out=tmp;
         BY rg7_20905 i_20905;
      RUN;

      DATA '||strip(memname)||'_divisions;
         SET tmp;
         BY rg7_20905;
         if last.rg7_20905;
      RUN;

      PROC DATASETS lib='||strip(libname)||' noprint;
         delete tmp;
      RUN;QUIT;');
run;
Frequent Contributor
Posts: 129

Re: Do same steps on 100 files, all files in a particular library

Posted in reply to NicholasKormanik
I see, I updated the code.
________________________

- Cheers -

Regular Contributor
Posts: 225

Re: Do same steps on 100 files, all files in a particular library

Fantastic, Oligolas.  Thank you!

 

I have a follow-up question, if I could.  I'll post it as a new question, however.

 

https://communities.sas.com/t5/Base-SAS-Programming/Do-same-basic-steps-on-200-variables-in-a-partic...

 

Nicholas

 

Frequent Contributor
Posts: 100

Re: Do same steps on 100 files, all files in a particular library

Posted in reply to NicholasKormanik
%macro AllOverAgain;
%do i=100 to 200;
proc sort data=have_&i.;
by variable_rank variable_continuous;
run;

data have_&i._divisions;
set have_&i.;
by variable_rank;
if last.variable_rank;
run;
%end;
%mend;

%AllOverAgain;
Super User
Super User
Posts: 7,997

Re: Do same steps on 100 files, all files in a particular library

Posted in reply to NicholasKormanik

This is a common problem, and one which higlights why it is not a good idea to split data up.  If you have one dataset with the identifier in the data, then the task (and all other tasks) are far similar utilising something called by group processing which is inbuilt in SAS Base.  Strongly recommended to use that.  For example, say I want to print your output, I could repeat that code for each table, or create messy macro code to do that, or I could just do:

data total;
  length datasource $200;
  set have_: indsname=tmp;
/* indsname takes the filename which is read in from the list of files with have_ prefix */
  datasource=tmp;
run;

proc sort data=total;
  by variable_rank variable_continuous;
run;

data total;
  set total;
  by variable_rank;
  if last.variable_rank;
run;

proc report data=total nowd;
  by datasource;
  title "File is: #byval1";
  columns _all_;
run;

Note you can shrink that code further but this is for illustration).  This takes all the datasets with have_ as prefix, puts them together and creates a variable for the dataset name.  Then is does your code, then it reports out each file.  Far simpler, will expand and shrink with the data available.  Very powerfull and a key skill in using SAS.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 124 views
  • 2 likes
  • 4 in conversation