topic Re: Macro Help: Loop through datasets and append in SAS Programming

Macro Help: Loop through datasets and append

Jacob — Fri, 08 Apr 2016 16:10:13 GMT

Hello all,

I have a library containing monthly datasets back to December 2006 and I would like to loop through each of them, perform my data step logic, and then append them together. I use macros but have never used like this before.

My files are all dated with the following naming convention: N4.BANK.PORT.NOTEyymm.FILE where yymm=0612 for December 2006.

My initial thoughts were to create macro variables for yy and mm and processes using iterative loops to call each file, for example:

%Macro

yy=06;

mm=01 to 12;

DATA PORT_DATA (KEEP=LN_ACCOUNT DER_RPT_DT);

SET PORT&&YY&MM..FILE;

IF SUBSTR(STRIP(LN_ACCOUNT),1,1) = 9;

DER_RPT_DT = PUT((MDY(&MM.,1,&YY.)),MMDDYY10.);

FORMAT DER_RPT_DATE MMDDYY10.;

RUN;

proc append;

next mm;

yy=yy+1;

end;

run;

% mend

My problem is that I'm not sure how to implement, or if this is even the right syntax. Please help me identify a solution!

Regards,

Jacob

SAS 9.4

Re: Macro Help: Loop through datasets and append

Reeza — Fri, 08 Apr 2016 16:16:01 GMT

In general, the start of creating a macro is working code for a single iteration. Is your data step provided below the working code you want to implement?

Re: Macro Help: Loop through datasets and append

Jacob — Fri, 08 Apr 2016 16:20:25 GMT

Hi Reeza, it is. the only thing missing is the proc append which I assume woudl be as follows:

proc append base = hist data=port_data force;

run;

Re: Macro Help: Loop through datasets and append

ballardw — Fri, 08 Apr 2016 16:35:52 GMT

If you don't have any other sets that start with PORT then this might be an alternative. It uses the NAME of the dataset to provide the information and combines all of the datasets at one time.

DATA PORT_DATA (KEEP=LN_ACCOUNT DER_RPT_DT);
     SET PORT: indsname=SetName;
     IF SUBSTR(STRIP(LN_ACCOUNT),1,1) = 9;
     DER_RPT_DT = PUT((MDY(substr(Setname,8,2),1,substr(Setname,6,2))),MMDDYY10.);
     DER_RPT_Date = MDY(substr(Setname,8,2),1,substr(Setname,6,2));
     FORMAT DER_RPT_DATE MMDDYY10.;
RUN;

The PORT: (note the : is a list of every dataset that starts with PORT ), the option INDSNAME creates a temporary variable, in this case SetName that will have values of PORT0101FILE PORT0102FILE PORT0103FILE plus the library at the front. You did not specify that so it is important to mention. If you used the 01 convention then you can find the values of the month and year using substring. The substr(Setname,8,2) ignores the library you would need to add the length of the libraryname plus one (for the .) to 8 and 6 respectively.

I provided two versions one string as you were currently doing and a SAS date value. Keep the one you want, I would recommend the actual date as sorting and other reporting will be ever so much easier.

Again, this will work if the only sets that start with PORT are the ones you want. If you have others you might try:

Set Port9: Port0: Port1: indsname= SetName;

to get those years starting with 9, 0 and 1 IF you don't have other things with that naming convention.

Re: Macro Help: Loop through datasets and append

Tom — Fri, 08 Apr 2016 16:37:16 GMT

To loop over dates it is best use the date interval functions like INTCK() and INTNX().

Here is code that will loop that will increment the macro variable MONTH by one month at a time and format its value in the form YYMM. (0612, 0701, .... 1604). I will leave the exercise of how to read one months data to you.

%macro loop(out=total,from=,to=);
%local fromdate todate i month ;
%let fromdate = %sysfunc(inputn(&from.01,yymmdd6));
%let todate = %sysfunc(inputn(&to.01,yymmdd6));
%put &=fromdate %sysfunc(putn(&fromdate,date9));
%put &=todate %sysfunc(putn(&todate,date9));
%do i= 0 %to %sysfunc(intck(month,&fromdate,&todate));
  %let month=%sysfunc(intnx(month,&fromdate,&i),YYMMN4);
/* Place code to read data for &month here */
  data current; set ....&month... ; .... run;
/* Now append the current month to the master file */
  proc append data=current base=&out force ; run;
%end;
%mend ;

proc delete data=total ; run;
%loop(out=total,from=0612,to=0705);

Re: Macro Help: Loop through datasets and append

Jacob — Fri, 08 Apr 2016 17:55:12 GMT

Thank you both Tom and ballardw. I'm going to give both a go and will mark completed once I am done!

I appreciate your time and help!

Re: Macro Help: Loop through datasets and append

Jacob — Fri, 08 Apr 2016 18:28:53 GMT

So, for the PORT: part... port is a libref assigned as follows: libname PORT "N4.Bank.Port.Note0612.FILE". There are 22 other directories besides the NOTEyymm files that come after "N4.Bank.Port." Such as .ACCTyymm.FILE, COMMyymm.FILE, etc...

How can I modify this to work with those?

Thank you!

Re: Macro Help: Loop through datasets and append

ballardw — Fri, 08 Apr 2016 19:48:42 GMT

@Jacob wrote:

So, for the PORT: part... port is a libref assigned as follows: libname PORT "N4.Bank.Port.Note0612.FILE". There are 22 other directories besides the NOTEyymm files that come after "N4.Bank.Port." Such as .ACCTyymm.FILE, COMMyymm.FILE, etc...

How can I modify this to work with those?

Thank you!

Please provide the example names in the SAS form of LIBRARY.DATASET. As it is, I'm not sure now if you have datasets named PORT.PORTYYMM..... or what. Or have you been reassigning the Library for each "file"

FILES in a directory aren't a concern as much as SAS Datasets in a LIbrary for the code I provided. Or are you doing something with a remote database that hasn't been mentioned.

It might help if you would run the following code and post the result:

proc datasets lib=PORT ; 
run;
quit;