topic Re: Import data from multiple years and months using Macro in SAS Programming

Import data from multiple years and months using Macro

changxuosu1 — Wed, 07 Mar 2018 19:26:37 GMT

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!

Re: Import data from multiple years and months using Macro

Tom — Wed, 07 Mar 2018 19:44:18 GMT

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.

Re: Import data from multiple years and months using Macro

changxuosu1 — Wed, 07 Mar 2018 23:03:23 GMT

thank you, Tom. Learned a lot from your reply!

Re: Import data from multiple years and months using Macro

changxuosu1 — Thu, 08 Mar 2018 04:42:57 GMT

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)