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!
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.
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.
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.