CODEWITHSUNDEEP
loopsmacrossasdo-loops
MrktMixologist
MrktMixologist

Reputation: 3

Stack Data Loop - SAS Macro

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;

Upvotes: 0

Views: 228

Answers (1)

DomPazz
DomPazz

Reputation: 12465

You need to change your second (&iterate > 1) statement to:

data merge1;
keep year period labor_force employment unemployment unemployment_rate state;
set merge1 &state;
run;

Otherwise, you are always creating a new data set named 'unemploy_merge' with the data from ITERATE=1 and the current file. That is why you only see the first and last at the end.

Upvotes: 0

Related Questions