DATA Step, Macro, Functions and more

Import data from multiple years and months using Macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Import data from multiple years and months using Macro

Hi Everyone, thank you for reading the post. I want to import quarterly data from 1993-2017. I created a macro to automate things. I'm a beginner in Macro. I created two macro variables and used two loops. One loops around the year, the other loops quarterly so that I can get data from March, June, September, and December for each year. The code I wrote somehow only imported data from December in every year.  Does anyone have any ideas? Thank you in advance!

 

libname conso "C:\Users\Chang Xu\Desktop\FIE Chang\Box Sync\FIE Chang\2nd paper\data analysis";
options mlogic;
options mprint;
options symbolgen;

/* create a macro to combine reports from every quarter and every year into one file*/
%macro year(starty=1993, endy=2017, increy= 1, startm=0331, endm=1231, increm = 300);
%local i;
%local j;
%do i = &starty %to &endy %by &increy;
%do j = &startm %to &endm %by &increm;
proc import out = conso.agloan&&i.&j
datafile = "C:\Users\Chang Xu\Desktop\FIE Chang\Box Sync\FIE Chang\2nd paper\0-data\FDIC\raw_about other attribute\All_Reports_&&i&&j.\All_Reports_&&i&&j._Small Business Loans.csv"
dbms = csv replace; getnames = yes;
datarow = 2;
run;
%end;
%end;
%mend;

%year(starty=1993,endy=2017, increy=1, startm=0331, endm=1231, increm = 300)

 

Thank you!


Accepted Solutions
Solution
‎03-07-2018 06:13 PM
Super User
Super User
Posts: 8,267

Re: Import data from multiple years and months using Macro

[ Edited ]
Posted in reply to changxuosu1

Turn on MPRINT option and look at what code you generated.

If you really need those leading zeros then make sure to add them back.

%do j=0331 to 1231 by 300 ;
   %let mmdd = %sysfunc(putn(&j,Z4));
   %put &=j  &=mmdd;
%end;

Is there really a file for 20170631 and 20170931 ? Those are invalid date strings.

 

But since your files are CSV files why are you using PROC IMPORT?  Why not just write a data step to read the data files?

You could probably read all of the files in one data step if they all have the same format.

View solution in original post


All Replies
Solution
‎03-07-2018 06:13 PM
Super User
Super User
Posts: 8,267

Re: Import data from multiple years and months using Macro

[ Edited ]
Posted in reply to changxuosu1

Turn on MPRINT option and look at what code you generated.

If you really need those leading zeros then make sure to add them back.

%do j=0331 to 1231 by 300 ;
   %let mmdd = %sysfunc(putn(&j,Z4));
   %put &=j  &=mmdd;
%end;

Is there really a file for 20170631 and 20170931 ? Those are invalid date strings.

 

But since your files are CSV files why are you using PROC IMPORT?  Why not just write a data step to read the data files?

You could probably read all of the files in one data step if they all have the same format.

Occasional Contributor
Posts: 12

Re: Import data from multiple years and months using Macro

thank you, Tom. Learned a lot from your reply!
Occasional Contributor
Posts: 12

Re: Import data from multiple years and months using Macro

Thank you so much, Tom. After your reply, I understand it didn't work previously because the leading 0 is omitted. Now it works (code below).

Do you know is there any way to create an array to store values of 0331 0630 0930 1231 and reference each one in a do loop, rather than repeat the same coding for all four cases? Something like 

 

%Array(mmdd, 331, 630, 930, 1231)

%do j = mmdd{1} %to mmdd{4}

 

I searched but no luck...thank you in advance....

 

 

 

options mlogic;
options mprint on;
options symbolgen;
/* create a macro to combine reports from every quarter and every year into one file*/
%macro year(starty=1993, endy=2017, increy= 1, startm=0331, endm=1231, increm = 300);q
%local i;
%local j;
%do i = &starty %to &endy %by &increy;
%let j=0331;
%let mmdd = %sysfunc(putn(&j,Z4));
%put &=j &=mmdd;
proc import out = work.agloan&&i.&j
datafile = "C:\Users\Chang Xu\Desktop\FIE Chang\Box Sync\FIE Chang\2nd paper\0-data\FDIC\raw_about other attribute\All_Reports_&&i&&mmdd.\All_Reports_&&i&&mmdd._Small Business Loans.csv"
dbms = csv replace; getnames = yes;
datarow = 2;
run;
%end;

%do i = &starty %to &endy %by &increy;
%let j=0630;
%let mmdd = %sysfunc(putn(&j,Z4));
%put &=j &=mmdd;
proc import out = work.agloan&&i.&j
datafile = "C:\Users\Chang Xu\Desktop\FIE Chang\Box Sync\FIE Chang\2nd paper\0-data\FDIC\raw_about other attribute\All_Reports_&&i&&mmdd.\All_Reports_&&i&&mmdd._Small Business Loans.csv"
dbms = csv replace; getnames = yes;
datarow = 2;
run;
%end;


%do i = &starty %to &endy %by &increy;
%let j=0930;
%let mmdd = %sysfunc(putn(&j,Z4));
%put &=j &=mmdd;
proc import out = work.agloan&&i.&j
datafile = "C:\Users\Chang Xu\Desktop\FIE Chang\Box Sync\FIE Chang\2nd paper\0-data\FDIC\raw_about other attribute\All_Reports_&&i&&mmdd.\All_Reports_&&i&&mmdd._Small Business Loans.csv"
dbms = csv replace; getnames = yes;
datarow = 2;
run;
%end;


%do i = &starty %to &endy %by &increy;
%let j=1231;
%let mmdd = %sysfunc(putn(&j,Z4));
%put &=j &=mmdd;
proc import out = work.agloan&&i.&j
datafile = "C:\Users\Chang Xu\Desktop\FIE Chang\Box Sync\FIE Chang\2nd paper\0-data\FDIC\raw_about other attribute\All_Reports_&&i&&mmdd.\All_Reports_&&i&&mmdd._Small Business Loans.csv"
dbms = csv replace; getnames = yes;
datarow = 2;
run;
%end;

%mend;
%year(starty=1993,endy=2017, increy=1, startm=0331, endm=1231, increm = 300)

 

 

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 129 views
  • 3 likes
  • 2 in conversation