I've used the relatively popular macro for importing many CSV files into SAS files. I then used a code to alter certain facets of the files. Each of these Datasets then had three columns with NameVar (name of food), Nutrient, and Amount. I wanted to turn this into a row so I could easily merge all of these files. The merge worked on some files and not others. The log is also giving an error that the file naming convention is not correct. Then I use the PROC APPEND to add all the files together, which works for some and not others, I believe this is because some of the files did not successfully transpose. Can you help me correct these errors? I've been working on this for several days and cannot figure out where I went wrong: /*Create Macro to Create a List of all the CSV files in the Folder*/
%global caps;
%let caps=/folders/myshortcuts/myfolder/Capstone;
libname capstone "&caps";
%macro list_files(dir, ext);
%local filrf rc did memcnt name i;
%let rc=%sysfunc(filename(filrf, &dir));
%let did=%sysfunc(dopen(&filrf));
%if &did eq 0 %then
%do;
%put Directory &dir cannot be open or does not exist;
%return;
%end;
%do i=1 %to %sysfunc(dnum(&did));
%let name=%qsysfunc(dread(&did, &i));
%if %qupcase(%qscan(&name, -1, .))=%upcase(&ext) %then
%do;
%put &dir\&name;
%let file_name = %qscan(&name, 1, .);
%put &file_name;
data _tmp;
length dir $512 name $100;
dir=symget("dir");
name=symget("name");
path=catx('\', dir, name);
the_name=substr(name, 1, find(name, '.')-1);
run;
proc append base=list data=_tmp force;
run;
quit;
proc sql;
drop table _tmp;
quit;
%end;
%else %if %qscan(&name, 2, .)=%then
%do;
%list_files(&dir\&name, &ext) %end;
%end;
%let rc=%sysfunc(dclose(&did));
%let rc=%sysfunc(filename(filrf));
%mend list_files;
/*Finish Macro and create code to import datasets, and also remove extraneous obs*/
%macro import_file(path, file_name, dataset_name);
data &dataset_name REPLACE;
INFILE "&path./&file_name." firstobs=5 dsd truncover end=last;
LENGTH Nutrient Amount $20 Namevar $50;
Namevar = "&file_name";
Namevar = substr(Namevar,1,(length(Namevar)-4));
INPUT Nutrient Amount;
if not last then
output;
run;
proc transpose data=&dataset_name out=&dataset_name;
VAR Amount;
ID Nutrient;
BY Namevar;
run;
proc append base = FOOD01 FORCE
new = &dataset_name;
run;
run;
%mend;
%list_files(/folders/myshortcuts/myfolder/food_data, csv);
/*This code will iterate through the files and import each of them with a new title*/
data _null_;
set list;
string=catt('%import_file(', dir, ', ', name, ', ', catt('food', put(_n_,
z2.)), ');');
call execute (string);
run;
... View more