Stacking Datasets - Macro Loop

[mrktmixologist]

Hi, I have 51 files (I pulled unemployment rates for all 50 states plus DC from the BLS), and I need to stack them on top of each other. I am working on a macro that will import the files and then stack them, but I am stuck on how I can write a loop to just stack them. I am only stacking the first and the last data sets, and nothing in between. I am not sure what I am doing wrong, any help will be much appreciated. Thanks in advance.

/*MACRO TO IMPORT TRANSLATED RAW DATA FILES*/

%macro import;

%do iterate = 1 %to &files;

  data _null_;

  set files(where = (file = &iterate));

      call symput('infile',left(trim(filename)));

  call symput('State',left(trim(state)));

  run;

  %put &infile;

  %put &state;

  /*IMPORT RAW DATAFILES*/

  proc import datafile = "&raw\&infile" out = &State dbms = xls replace;

  getnames = yes; namerow = 11; datarow = 12;

  run;

  /*CREATE A NEW STATE COLUMN*/

  data &State;

  set &State;

  State = "&State";

  run;

  %if &iterate = 1 %then %do;

  data merge1;

  keep year period labor_force employment unemployment unemployment_rate state;

  format year 4. period $3. labor_force employment unemployment comma12.2 unemployment_rate 4.2

  state $24.;

  set &state;

  run;

  %end;

  %if &iterate > 1 %then %do;

  data unemploy_merge;

  keep year period labor_force employment unemployment unemployment_rate state;

  set merge1 &state;

  run;

  %end;

%end;

%mend import;

[Reeza]

Use a naming convention instead, i.e.

%macro import;

%do iterate = 1 %to &files;

  data _null_;

  set files(where = (file = &iterate));

      call symput('infile',left(trim(filename)));

  call symput('State',left(trim(state)));

  run;

  %put &infile;

  %put &state;

  /*IMPORT RAW DATAFILES*/

  proc import datafile = "&raw\&infile" out = input_&State dbms = xls replace;

  getnames = yes; namerow = 11; datarow = 12;

  run;

%end;

%mend;

%import;

*Then append them all in a single step, using a colon operator.

data final;

set input_: indsname=source;

file_name=source;

run;

[Reeza · Accepted solution]

However, the issue is you're always merging merge1 (first data) with the new (latest) data, not to the last appended data.

Change it to merge1. 

Also, a Proc append would be better, as its more efficient and doesn't need the conditional macro statement.

proc append base=unemploy_merge data=&state force;

run;

More than one way to solve this issue.