Macros referencing filenames 1

[whoffman]

Help. I'm new to SAS and I have written a macro to process against several external Excel files. I cannot figure out how to pass the different filenames to the macro to process one at a time. I have a SAS dataset (180 observations) with the variable "fn" that contains the filename I want the macro to open. I keep getting a "cannot resolve apparent symbolic reference" error.
Any clues as to how o go about this? Thanks in advance!

 

[ChrisW75]

This is a piece of code I use for doing this, I keep this particular piece as a template for anytime I want to do a loop controlled by the contents of a dataset.

* Get list of agent codes that I'm outputting *;
proc sort data=full_dat(keep=agent) out=agents nodupkey;
  by agent;
run;

* Use array to create list of macro variables agnt1, agnt2 etc *;
*  each one contains an agent number                           *;
data _null_;
  set agents nobs=num_agents end=eof;
  file print;
  array agnt{99} $2;
  retain agnt;

  agnt{_n_} = agent;
  if eof then do;
    call symput('agnt_cnt',num_agents);
    do i = 1 to num_agents;
      rec = compress('agnt' || i);
      call symput(rec,agnt{i});
    end;
  end;
run;


* Macro to output the data I want *;
%macro outpt(o_agnt);
data _null_;
  set full_dat(where=(agent="&o_agnt")); 
  file blah recfm=v lrecl=4000;
  put @1 allrec;
run;
%mend outpt;

* Macro to call output macro with correct agent code *;
*Row by row breakdown:-
  Loop from 1 t limit set by number of different agents
  let macro variable blah equal &agnt1, &agnt2 etc
  call macro outpt and pass it the value of the agent code for this iteration
*;
%macro loop;
  %do i=1 %to &agnt_cnt;
    %let blah = &&agnt&i;
    %outpt(&blah);
  %end;
%mend loop;

%loop;

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[whoffman]

Thanks so much for the code! I modified it a bit and it works great for me. I'm an old VB programmer, so this SAS stuff is confusing the heck out of me. Things that used to take a few statements, now take ten times that! I really don't think it was meant to be a data manipulation language - its probably great for statistics though!

 

[ChrisW75]

I also went from VB to SAS, but wasn't a hugely experienced VB programmer. I tend to find SAS pretty easy myself, I think, as with most languages, once you get the key, it starts to fall into place. Data manipulation is pretty much all I do day in, day out.
I actually find VBs methods for data manipulation kind of hard to understand these days, I guess it's just the fact that I've gotten so used to SAS's way of doing things...
Stay with it, you'll get the hang of it.
Do you have other SAS programmers there that you can tap for knowledge? If so, and if they're good, it sometime help to have them review programs you've written that actually work and get any pointers. When I first learnt, that was the way I was taught, they'd give me a program to write or edit, then review what I'd done and show me all the things I could do in different (usually more efficient) ways.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.