Date:         Thu, 19 Jun 2003 08:20:34 -0400
Reply-To:     "Fehd, Ronald J. (PHPPO)" <rjf2@CDC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Fehd, Ronald J. (PHPPO)" <rjf2@CDC.GOV>
Subject:      Re: how to shorten the typing in SQL
Comments: cc: Liyan Liu <liyanliu888@YAHOO.COM>
Content-Type: text/plain

> From: Liyan Liu [mailto:liyanliu888@YAHOO.COM]
> Our data base has 13 diagnoses, princ_diag,
> admit_diag_cd, diag_1-diag_11. I often need to
> download data from mainframe using SQL, if any of the
> 13 diagnoses meets my list of ICD9 codes, I want to
> download them. My problem is my list of ICD9 codes are
> very long(actuall list of ICD9 codes are much longer
> than shown in the following example). How could I
> shorten the typing, is it possible to use macro or
> array to repeat the 13 diagnoses?
>
> *Here is what I usually do;
>    PROC SQL;
>    CONNECT TO DB2(SSID=DSN2);
>    CREATE TABLE CTRL.CTRLPOOL AS
>      SELECT *
>      FROM CONNECTION TO DB2
>       (
>
> SELECT *

I have taken the liberty of reformating your where phrase
so that it clearly highlights the variable name

          PRINC_DIAG='412'
or '410'<=PRINC_DIAG<='41099'
or '428'<=PRINC_DIAG<='4289 '
or        PRINC_DIAG IN ('4293','40201','40211','40291')

or        DIAG_1='412'
or '410'<=DIAG_1<='41099'
or '428'<=DIAG_1<='4289 '
or        DIAG_1 IN ('4293','40201','40211','40291')

%macro SELECT(VAR=);%*note macro function: returns phrase;
%*note no beginning OR;
%*note this selection order may be optimized
  by placing more freqent code at top of list;
          &VAR.='412'
or '410'<=&VAR.<='41099'
or '428'<=&VAR.<='4289 '
or        &VAR. IN ('4293','40201','40211','40291')
%MEND;

> *... Then repeat the codes for diag_3-diag_11;

   %SELECT(VAR=PRINC_DIAG)%*NOTE: no closing semicolon;
or %*NOTE: each series is connected with OR;
   %SELECT(VAR=PRINC_DIAG)
or %SELECT(VAR=ADMIT_DIAG_CD)
or %SELECT(VAR=DIAG_1)
...
or %SELECT(VAR=DIAG_11)


> If I can find a way to shortening the lengthy typing,
> my life would be much simpler and happier.

just what macros were designed to save for programmers

Ron Fehd  the macro maven  CDC Atlanta GA USA RJF2@cdc.gov

do not fold, spindle, or mutilate -- IBM punch card
... the amount of typing required

"Nothing is particularly hard
if you divide it into small jobs."
- Henry Ford, Industrialist