topic Re: How can I automate the macro to import multiple annual and quarterly files ? in SAS Programming

How can I automate the macro to import multiple annual and quarterly files ?

AP718 — Thu, 23 Jul 2020 16:24:34 GMT

How can I better automate this code to import multiple files to create quarterly datasets as described below. Thank you!

%MACRO YEARQTR (YR,QTR,MON);

PROC IMPORT DATAFILE= "C:\Users\&SYSUSERID\&MON. &YR. ABC File.xlsx" 
						OUT= ABC_&YR._Q&QTR
						DBMS=xlsx REPLACE;
						GETNAMES=YES;
RUN;

%MEND;

%YEARQTR (19,1,Jan);
%YEARQTR (19,2,Apr);
%YEARQTR (19,3,Jul);
%YEARQTR (19,4,Oct);

%YEARQTR (18,1,Jan);
%YEARQTR (18,2,Apr);
%YEARQTR (18,3,Jul);
%YEARQTR (18,4,Oct);

%YEARQTR (17,1,Jan);
%YEARQTR (17,2,Apr);
%YEARQTR (17,3,Jul);
%YEARQTR (17,4,Oct);

%YEARQTR (16,1,Jan);
%YEARQTR (16,2,Apr);
%YEARQTR (16,3,Jul);
%YEARQTR (16,4,Oct);

Re: How can I automate the macro to import multiple annual and quarterly files ?

Reeza — Thu, 23 Jul 2020 16:35:31 GMT

CALL EXECUTE.

Run this first. Verify that the str calculated looks like your macro execution lines below. If not adjust the code until it does. Once it's correct, uncomment the CALL EXECUTE line and it will execute the commands.

Blog describing usage

https://blogs.sas.com/content/sgf/2017/08/02/call-execute-for-sas-data-driven-programming/

Documentation reference

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n1q1527d51eivsn1ob5hnz0yd1hx.htm&docsetVersion=9.4&locale=en

data run_macros;

do year=2016 to 2019;
    do quarter=1 to 4;

          year2digit = substrn(year, 3, 2);
          month_name = put(mdy(quarter*3, 1, year), monname3.);
          str = catt('%YEARQTR(', year2digit, ', ', quarter, ', ', month_name, ');');
         *call execute(str);

    end;
end;

run;

@AP718 wrote:

How can I better automate this code to import multiple files to create quarterly datasets as described below. Thank you!

%MACRO YEARQTR (YR,QTR,MON);

PROC IMPORT DATAFILE= "C:\Users\&SYSUSERID\&MON. &YR. ABC File.xlsx" 
						OUT= ABC_&YR._Q&QTR
						DBMS=xlsx REPLACE;
						GETNAMES=YES;
RUN;

%MEND;

%YEARQTR (19,1,Jan);
%YEARQTR (19,2,Apr);
%YEARQTR (19,3,Jul);
%YEARQTR (19,4,Oct);

%YEARQTR (18,1,Jan);
%YEARQTR (18,2,Apr);
%YEARQTR (18,3,Jul);
%YEARQTR (18,4,Oct);

%YEARQTR (17,1,Jan);
%YEARQTR (17,2,Apr);
%YEARQTR (17,3,Jul);
%YEARQTR (17,4,Oct);

%YEARQTR (16,1,Jan);
%YEARQTR (16,2,Apr);
%YEARQTR (16,3,Jul);
%YEARQTR (16,4,Oct);

Re: How can I automate the macro to import multiple annual and quarterly files ?

AP718 — Thu, 23 Jul 2020 16:58:23 GMT

Thank you. By doing it this way in a dataset can I get rid of the repeating parameter lines ? I am looking for a way to create a loop or shorter macro for the year, months and quarters parameters.

Re: How can I automate the macro to import multiple annual and quarterly files ?

AMSAS — Thu, 23 Jul 2020 17:09:30 GMT

@AP718

Here's some example code. Basically I have created a macro to call your macro, you will need to replace my:
%put yearqtr(&year,&qtr,&mon) ;

With the call to your maco

%macro myload(startYear, endYear) ;
	proc format ;
		value myMonth 
			1="Jan"
			2="Apr" 
			3="Jul"
			4="Oct" ;
	run ;
	
	%do year=&startYear %to &endYear ;
		%do qtr=1 %to 4 ;
			%let mon=%sysfunc(putn(&qtr,myMonth.)) ;
			
			%put yearqtr(&year,&qtr,&mon) ;
		%end ;
	%end ;
	
%mend ;

%myload(15,18) ;
run ;

Re: How can I automate the macro to import multiple annual and quarterly files ?

ballardw — Thu, 23 Jul 2020 17:12:43 GMT

@AP718 wrote:
Thank you. By doing it this way in a dataset can I get rid of the repeating parameter lines ? I am looking for a way to create a loop or shorter macro for the year, months and quarters parameters.

You in effect build the needed code using the data set values to build the desired string holding the file names and output data set names.

Similar approaches could use just years actual dates or lists of specific values in the do loops.

I suggest that you add the SCANTEXT option to the import code to examine more rows of data before setting the lengths of character variables. The Proc Import procedure only uses the values of the first 20 rows by default to set lengths and if the first rows of file have a column that is not always used you could end up with a length of 1 character for the variable and lots of truncated data.

Warning: Unless your source of XLSX files is extremely consistent you may end up spending a lot of time fixing data imported using proc import because each separate call to Proc Import makes separate decisions based on the content of the file for data types, lengths and even formats, especially in the case of date/time/datetime values. So when you go to combine any of these sets you may get warnings of possibly truncated data from different length strings or errors because of incompatible data types.

Re: How can I automate the macro to import multiple annual and quarterly files ?

Reeza — Thu, 23 Jul 2020 17:19:57 GMT

Yes, it entirely gets rid of your macro loops. I find CALL EXECUTE simpler to work with and easier to debug in the long run, but that's very much a personal preference.

Re: How can I automate the macro to import multiple annual and quarterly files ?

Tom — Thu, 23 Jul 2020 17:56:07 GMT

You can use INTNX() to increment by quarter. You need to provide a start date and an end date. Let's call those STARTDT and ENDDT.

%let startdt='01JAN2016'd;
%let enddt='01OCT2019'd;

These things are easier in data step than in macro code:

data _null_;
  do offset=0 to intck('qtr',&startdt,&enddt);
    dt=intnx('qtr',&startdt,offset);
    yr=year(dt)-2000;
    qtr=qtr(dt);
    mon=put(dt,monname3.);
    call execute(cats('%nrstr(%yearqtr)(YR=',yr,',QTR=',qtr,'MON=',mon,')'));
  end;
run;

But you could do it in a macro.

%macro qtr(startdt,enddt);
  %local offset dt yr qtr mon ;
  %do offset=0 %to %sysfunc(intck(qtr,&startdt,&enddt));
    %let dt=%sysfunc(intnx(qtr,&startdt,&offset));
    %let yr=%eval(%sysfunc(year(&dt)))-2000);
    %let qtr=%sysfunc(qtr(&dt));
    %let mon=%sysfunc(putn(&dt,monname3.));
    %yearqtr(YR=&yr,QTR=&qtr,MON=&mon)
  %end;
%mend;
%qtr(&startdt,&enddt);