DATA Step, Macro, Functions and more

Stacking Datasets - Macro Loop

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Stacking Datasets - Macro Loop

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;


Accepted Solutions
Solution
‎02-17-2014 10:37 PM
Super User
Posts: 19,806

Re: Stacking Datasets - Macro Loop

Posted in reply to mrktmixologist

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.

View solution in original post


All Replies
Super User
Posts: 19,806

Re: Stacking Datasets - Macro Loop

Posted in reply to mrktmixologist

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;

Solution
‎02-17-2014 10:37 PM
Super User
Posts: 19,806

Re: Stacking Datasets - Macro Loop

Posted in reply to mrktmixologist

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 919 views
  • 0 likes
  • 2 in conversation