BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
changxuosu1
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

3 REPLIES 3
Tom
Super User Tom
Super User

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.

changxuosu1
Obsidian | Level 7
thank you, Tom. Learned a lot from your reply!
changxuosu1
Obsidian | Level 7

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)

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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