Need help with this loop

[ecam]

Im running this macro and the point is to loop through the files specified and append it into the out.new data set.   right now the only thing that ends up there is the last set op_s_rvlr.   I know its working but its not appending to the final  table.  What am I missing

libname out '/***/***/users/ecambero';

data out.new;
set _null_;
run;

%macro abcd(PF,seg);

libname in "/idn/ccar_fin/***/mdl/US/BB/Output_Data/base/&PF.";

data out.new;
set out.new in.&seg.;
flag = "&seg";
run;

%mend;

%abcd(OPEN_L,op_l_tx);
%abcd(OPEN_L,op_l_rv);
%abcd(OPEN_S,op_s_rvlr);

proc print data=out.new ;

[Astounding]

You need to change the end of the macro.  The last step now looks like this:

data out.new;
set out.new in.&seg.;
flag = "&seg";
run;

Change it in this fashion:

data new;

length flag $ 9;
set in.&seg.;

flag = "&seg";
run;

proc append data=new base=out.new;

run;

That will add the newly created data set to the end of the permanent data set.

Before doing this, delete out.new.  Any new information gets appended to what is already there.  It is not necessary for out.new to exist for these changes to work.

[ecam]

Thanks..    i made the changes.    but now im getting the data will not append to the base file.  I created this blank and I was expecting SAS to automatically add the fields similar to the set statement when combining files.   I dont want to use "FORCE" and I do expect the files not to be completely identical since im going across years in this data pull

Sample error 

WARNING: Variable DBB was not found on BASE file. The variable will not be added to the BASE file.
ERROR: No appending done because of anomalies listed above. Use FORCE option to append these files.

data out.new;
set _null_;
run;

data new;

length flag $ 9;
set in.&seg.;

flag = "&seg";
run;

proc append data=new base=out.new;

run;

[Astounding]

Don't create a blank.  Delete it entirely.  Sorry if that wasn't clear.

However .... if you don't expect to the new files to have the same structure, APPEND is not the best route.  Just change the name of the output data set from NEW to &SEG.  Then OUTSIDE of the macro, use a DATA step to combine the data sets:

data out.new;

set op_l_tx op_l_rv op_s_rvlr;

run;

However, if you are going that route, it opens up new possibilities.  You could simply use a single step with no macro language:

data out.new;

length flag $ 9;

set in.op_l_tx (in=in1)

in.op_l_rv (in=in2)

in.op_s_rvlr (in=in3);

if in1 then flag='op_l_tx';

else if in2 then flag='op_l_rv';

else flag='op_s_rvlr';

run;

[ecam]

I

---

@Astounding wrote:

Don't create a blank.  Delete it entirely.  Sorry if that wasn't clear.

However .... if you don't expect to the new files to have the same structure, APPEND is not the best route.  Just change the name of the output data set from NEW to &SEG.  Then OUTSIDE of the macro, use a DATA step to combine the data sets:

data out.new;

set op_l_tx op_l_rv op_s_rvlr;

run;

However, if you are going that route, it opens up new possibilities.  You could simply use a single step with no macro language:

data out.new;

length flag $ 9;

set in.op_l_tx (in=in1)

in.op_l_rv (in=in2)

in.op_s_rvlr (in=in3);

if in1 then flag='op_l_tx';

else if in2 then flag='op_l_rv';

else flag='op_s_rvlr';

run;

---

I have 200 datasets    so this wont work on that.   ill try without the null table 

[s_lassen]

You can also read the data in one fell swoop, using the INDSNAME option:

libname inL "/idn/ccar_fin/***/mdl/US/BB/Output_Data/base/OPEN_L";
libname inS "/idn/ccar_fin/***/mdl/US/BB/Output_Data/base/OPEN_S";
data out.new;
  set
    inL.op_l_tx
    inL.op_l_rv
    inS.op_s_rvlr INDSNAME=dsn;
  flag = scan(dsn,2,'.');
run;

[ecam]

The problem with this is.. i have to go through folders and years... so the file is dynamically reading the paths there are over 40 directories and 30 files per directory i need to combine using this macro

[ballardw]

---

@ecam wrote:
The problem with this is.. i have to go through folders and years... so the file is dynamically reading the paths there are over 40 directories and 30 files per directory i need to combine using this macro

---

Since you are apparently using SAS data sets then one approach to the problem is does each of the directories have the same names for the datasets? Do you want to combine all of the datasets in all of the directories?

A single SAS library can refer to multiple directories. Your SASHELP library is one example. So if the names of the individual data sets are not duplicated you could use a single library.

The syntax looks like

libname mylib ('C:\path\dir1' 'C:\path\dir2' 'C:\path\dir3'  'C:\otherpath\otherdir');

list syntax also can help. If you use something like "mylib.o:" (please note the colon after the o) then a SET statement will use ALL of the data sets that start with o in the library. You can test this with the following code:

data work.junk;
   set sashelp.c: ;
run;

which appends all of the datasets in SASHELP whose names start with the letter c.

HOWEVER before going down this path you need to verify some things about commonly named variables existing in your data sets. First is do they all have the same lengths? If not you are likely to suffer some loss of data. Second are they all of the same data type.

If you have any variable with the same name and different types then any approach to appending the data will fail as a variable can only have one date type.

[ecam]

Question on the combined library. If I wanted it to read only files that end with “ratio”. How would i code that


I probably going to have to go down the set route since the files are all over the place. I need to combine all the datasets then standardize them.

[ecam]

@s_lassen. This could work. But I would need it to only read certain files that contain “ratio” in the file name. How would I isolate that ?

[s_lassen]

The way to go is probably to assign your libraries, and then use dictionary.tables to get the names of the relevant datasets:

libname inL "/idn/ccar_fin/***/mdl/US/BB/Output_Data/base/OPEN_L";
libname inS "/idn/ccar_fin/***/mdl/US/BB/Output_Data/base/OPEN_S";

proc SQL noprint;
  select catx('.',libname,memname) into :indata separated by ' '
  from dictionary.tables 
  where libname in('INL','INS') 
    and memname ? 'RATIO';
quit;

data out.new;
  set &indata INDSNAME=dsn;
  flag = scan(dsn,2,'.');
run;

 But you may want to keep all of the INDSNAME variable, so that you know which library the data came from as well.