Here's the shell of a macro that loops through the values of a var in a dataset:

%macro loopy(var = , dset = ) ; proc sql noprint ; * Select out the var names of interest. ; select distinct &var into :n1-:n9999 from &dset ;

* How many rows were there? ; %let num_rows = &SQLOBS ;

%do i = 1 %to &num_rows ; %let this_one = &&n&i ; %put Working on name &this_one ; <<pass &this_one into your macro here>> %end ; quit ; %mend loopy ;

Note that you've got to make sure the upper limit on that :n var is high enough to get all your recs.

HTH,

-Roy

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Kevin F. Spratt Sent: Friday, February 08, 2008 1:18 PM To: SAS-L@LISTSERV.UGA.EDU Subject: using a SAS data set to inform a macro

I have written a program that uses proc compare to look for differences in data entry.

1. I import the access table, which includes both entries in the sample table, 2. Split the entries into two separate SAS data sets, 3. Make sure sure any cases that don't have data for both entries are not included, 4. Dump those cases that aren't included into a separate data set 5. Run the compare procedure. 6. Print the output to a file named according so the access table name.

My problem is that I need to do this for 14 separate tables and I will need to do this regularly as these tables continue their growth.

My "plan" is to create a separate SAS data set that includes the Access table names and several of the variable names from each table that are needed for the proc compare program I have created to work. My hope is that I can create a macro that will loop through this SAS table one record at a time where each record provides the keywords needed to create the appropriate BASE and COMPARE data sets for each table.

I have written the macro that does the work, for any given table when I input the correct keywords, but I can't figure out how to inform the macro based on the SAS data set I have created that summarizes the table and I'm not sure about how to loop through through the tables.

I am doing this work with SAS 9.1.3 with service pack 3 in the Windows XP Professional environment, if that makes any difference.

As always, any help greatly appreciated.

______________________________________________________________________

Kevin F. Spratt, Ph.D. Department of Orthopaedic Surgery Dartmouth Medical School One Medical Center Drive DHMC Lebanon, NH USA 03756 (603) 653-6012 (voice) (603) 653-6013 (fax) Kevin.F.Spratt@Dartmouth.Edu (e-mail) _______________________________________________________________________