Date:         Tue, 1 Dec 2009 16:54:48 -0500
Reply-To:     "Fehd, Ronald J. (CDC/CCHIS/NCPHI)" <rjf2@CDC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Fehd, Ronald J. (CDC/CCHIS/NCPHI)" <rjf2@CDC.GOV>
Subject:      Re: PROC SQL & Macro Quoting
In-Reply-To:  <bfed04db0912011339h305882c2o294a4511c830313e@mail.gmail.com>
Content-Type: text/plain; charset=us-ascii

Tiny Url: http://tinyurl.com/5p3j2z for this page

http://www.sascommunity.org/wiki/List_Processing_Basics_Creating_and_Usi
ng_Lists_of_Macro_Variables

http://tinyurl.com/6mmqpj for paper

http://www2.sas.com/proceedings/forum2007/113-2007.pdf

SGF 2007, paper 113, section: Hands On Workshops

List Processing Basics: Creating and Using Lists of Macro Variables
* Ronald J. Fehd
* Art Carpenter

the above wiki page has a .zip with the program examples in the paper

what I notice: you are creating too many macro arrays

recommendation: write one subroutine which handles one filename at a
time

you have the code working to create and handle the array of filenames
you can use that to call your subroutine.

Ron Fehd  the macro maven  CDC Atlanta GA USA RJF2 at cdc dot gov
Ron Fehd  the module/routine/subroutine maven  CDC Atlanta GA USA RJF2
at cdc dot gov


> -----Original Message-----
> From: owner-sas-l@listserv.uga.edu
> [mailto:owner-sas-l@listserv.uga.edu] On Behalf Of Craig Johnson
> Sent: Tuesday, December 01, 2009 4:40 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: PROC SQL & Macro Quoting
>
> I'm creating a macro to check data files for errors.  I'm
> storing the needed
> information to check the data files in the file WhereClause.
> This file
> stores FileName, IDField, Field, WhereClause, and Error.  I
> then query this
> table and use it to look through data checks on various files.
>
>
>
> 1)       To begin I query the WhereClause for unique file
> names and store
> this in Filenames.
>
> 2)      Next I Query the WhereClause for all the IDField, Field,
> WhereClause, and Errors where FileName is equal to the first
> file name in
> the list.  I sort the results of the query  for each variable
> in a temp
> variable.
>
> 3)      I then looping through each data file with the where
> clauses for
> that data file. When errors are found, they are inserted into
> a new dataset
> that functions as a log.
>
>
>
> The issue I'm running into is I'm not sure how to properly quote the
> WhereClause and Errors variables because they have spaces.
>
>
>
> My two main problems
>
> 1)      I would like to store the variables with quotes
> around them during
> the SELECT INTO :VAR SEPARATED BY ' ' statement but I'm not
> sure how to do
> this or if it's possible
>
> 2)      I'm not sure how to properly scan for a quoted string
> so grabs the
> whole string and properly evaluates the text so it can be
> entered into the
> log.
>
>
>
> The code I'm working on is below........it isn't complete yet so
> I'm sure there
> are errors.  Any help would be greatly appreciated.
>
>
>
> *%macro* *WhereStatements*;
>
> /*Create an Error Log*/
>
> data work.&SysDay._Errors;
>
> set _Null_;
>
> attrib      ID                length=*8*          format=Best12.;
>
> attrib      FileName          length=$*80*;
>
> attrib      FieldID           length=$*40*;
>
> attrib      Field             length=$*40*;
>
> attrib      Error             Length=$*80*;
>
> run;
>
>
>
> /*Query Where File for Unique File Names*/
>
> PROC SQL NOPRINT;
>
> SELECT UNIQUE(FILENAME)
>
> INTO :FILENAMES SEPARATED BY ' '
>
> FROM MYLIB.WHERECLAUSE;
>
> QUIT;
>
> %Put These are the unique file names: &FILENAMES;
>
>
>
> /*First Loop: Find all the where clauses for the first SAS File*/
>
> %let i=1;
>
> %let FileName=%scan(&FileNames, &i);
>
> %do %while (&FileName ne);
>
>
>
>       PROC SQL NOPRINT;
>
>       SELECT      Field, IDField, Whereclause, Error
>
>       INTO  :FieldVars SEPARATED BY ' ', :IDFields   SEPARATED BY ' ',
>
>                   :WhereVars SEPARATED BY ' ', :ErrorVars
> SEPARATED BY ' '
>
>       FROM Mylib.Where
>
>       WHERE FileName = "&FileName";
>
>       QUIT;
>
>
>
>       %put These Are the Field Variables: &Fieldvars;
>
>       %put These are the ID Fields: &IDFields;
>
>       %put These Are the Where Statements: &WhereVars;
>
>       %put These Are the Error Statements: &ErrorVars;
>
>
>
>             /*Second Loop: Loop through where statements,
> look for errors,
> insert errors into log*/
>
>             %let x=1;
>
>             %let FieldVar=%Scan(&FieldVars, &x);
>
>             %let IDField=%Scan(&IDFields, &x);
>
>             %let WhereVar=%Scan(&WhereVars, &x);
>
>             %let ErrorVar=%Scan(%Str(&ErrorVars), &x);
>
>             %do %while (&FieldVar ne AND &IdField ne AND
> &WhereVar ne AND
> &ErrorVar ne);
>
>
>
>             PROC SQL;
>
>             INSERT INTO work.&SysDay._Errors (ID, FileName,
> FieldID, Field,
> Error)
>
>             SELECT &IDField,
>
>                   CASE
>
>                   When &IDField ne *.* Then "&FileName"
>
>                   Else 'FileName Error'
>
>                   End as FileName,
>
>                   CASE
>
>                   When &IDField ne *.* Then "&IDField"
>
>                   Else 'FieldID Error Error'
>
>                   End as FieldID,
>
>                   CASE
>
>                   When &IDField ne *.* Then "&FieldVar"
>
>                   Else 'Field Error'
>
>                   End as Field,
>
>                   CASE
>
>                   When &IDField ne *.* Then "&ErrorVar"
>
>                   Else 'Error ERROR!?!?'
>
>                   End as Error
>
>             FROM MyLib.&FileName
>
>             WHERE &FieldVar &WhereVar ;
>
>             QUIT;
>
>
>
>             %Let x=%eval(&x+1);
>
>             %let FieldVar=%Scan(&FieldVars, &x);
>
>             %let IDField=%Scan(&IdFields, &x);
>
>             %let WhereVar=%Scan(&WhereVars, &x);
>
>             %let ErrorVar=%Scan(&ErrorVars, &x);
>
>             %End;
>
>
>
> %let i=%eval(&i+1);
>
> %let FileName=%scan(&FileNames, &i);
>
> %end;
>
> *%Mend* WhereStatements;
>
>
>
> %*WhereStatements*;
>
>