topic Re: Stacking Datasets - Macro Loop in SAS Programming

Stacking Datasets - Macro Loop

mrktmixologist — Tue, 18 Feb 2014 01:56:14 GMT

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;

%mend import;

Re: Stacking Datasets - Macro Loop

Reeza — Tue, 18 Feb 2014 03:33:43 GMT

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;

Re: Stacking Datasets - Macro Loop

Reeza — Tue, 18 Feb 2014 03:37:22 GMT

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.