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;
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.
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.