DATA Step, Macro, Functions and more

Macro variable "where_clause"

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

Macro variable "where_clause"

Please see the log below. I’m trying to set up a “where_clause” macro variable that generates output based on what values an end user specifies in a stored process.  The way the macro is set up now it seems it is not allowing the users to leave some parameters intentionally blank. For instance you’ll notice I specified “CIRCUIT” and “filedate_min” and “filedate_max” but I wanted to leave the other variables blank.  I wonder if I need additional statements in my code?

>>> SAS Macro Variables:

CIRCUIT=1

CIRCUIT_COUNT=1

DISPOSITION_COUNT=0

DISTRICT_COUNT=0

DOCKET=

FILEDATE_MAX=31Dec2009

FILEDATE_MAX_LABEL=December 31, 2009

FILEDATE_MIN=01Jan2009

FILEDATE_MIN_LABEL=January 01, 2009

NOS_COUNT=0

TERMDATE_MAX=

TERMDATE_MIN=

_APSLIST=Circuit,Circuit_count,District_count,Docket,Filedate_max,Filedate_max_label,Filedate_min,Filedate_min_label,Termdate_max,Termdate_min,NOS_count,Disposition_count,_odsdest,_debug,_srvport,_grafloc,_srvname,_reqmeth,_htcook,_htua,_url,_version,_rmt

     host,_program,_username,_rmtaddr,_result,_metaperson,_metauser,_metafolder,_client,_SECUREUSERNAME

_CLIENT=StoredProcessService 9.2; JVM 1.5.0_15; Windows Vista (amd64) 6.1

_DEBUG=log

_GRAFLOC=/sasweb/graph

_HTCOOK=JSESSIONID=8B27C0C897600B0CF7E2882A741B99AD

_HTUA=Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; GTB7.2; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729)

_METAFOLDER=/Users/FJC Research/IDB/

_METAPERSON=webanon

_METAUSER=webanon@saspw

_ODSDEST=HTML

_PROGRAM=/Users/FJC Research/IDB/IDB Civil

_REPLAY="&_URL?_sessionid=483A209B-0031-41FF-8486-80103A15D8FC&_program=replay&_entry=&_TMPCAT.."

_REQMETH=GET

_RESULT=STREAM

_RMTADDR=169.254.95.120

_RMTHOST=169.254.95.120

_SECUREUSERNAME=webanon

_SRVNAME=fjcsas.ad.fjc.dcn

_SRVPORT=8080

_TMPCAT=APSWORK.TCAT0179

_URL=/SASStoredProcess/guest

_USERNAME=webanon@saspw

_VERSION=Version 9.2 (Build 420)

1          options nosource source2 center notes nodate nonumber ls=195 formchar='|----|+|---+=|-/\<>*' pagesize=40 noovp nomprint nomlogic nosymbolgen; title; footnote;

                                                                                          The SAS System

NOTE: %INCLUDE (level 1) file C:\Users\amason\Stored\IDBCivil.sas is file C:\Users\amason\Stored\IDBCivil.sas.

3         +

4         +%stpbegin;

5         +libname IDB "/res/IDB/updates";

NOTE: Libref IDB was successfully assigned as follows:

      Engine:        V9

      Physical Name: C:\res\IDB\updates

6         +

7         +

8         +%macro data();

9         +data work.test;

10        +set idb.cv00on;

11        +%local where_clause;

12        +

13        +%let where_clause=;

14        +

15        +%if %length(&CIRCUIT) >= 0 %then %do;

16        +

17        +   %if %length(&where_clause) > 0 %then %let where_clause = &where_clause and;

18        +

19        +   %let where_clause = &where_clause (CIRCUIT =&CIRCUIT);

20        +

21        +%end;

22        +

23        +%if %length(&DISTRICT) >= 0 %then %do;

24        +

25        +   %if %length(&where_clause) > 0 %then %let where_clause = &where_clause and;

26        +

27        +   %let where_clause = &where_clause (DISTRICT = "&DISTRICT");

28        +

29        +%end;

30        +

31        +%if %length(&DOCKET) >= 0 %then %do;

32        +

33        +   %if %length(&where_clause) > 0 %then %let where_clause = &where_clause and;

34        +

35        +   %let where_clause = &where_clause (DOCKET = "&DOCKET");

36        +

                                                                                          The SAS System

37        +%end;

38        +

39        +

40        +%if %length("&FILEDATE") >= 0 %then %do;

41        +

42        +   %if %length(&where_clause) > 0 %then %let where_clause = &where_clause and;

43        +

44        +   %let where_clause = &where_clause (FILEDATE between "&FILEDATE_min"d and "&FILEDATE_max"d);

45        +

46        +%end;

47        +

48        +

49        +

50        +%if %length("&TERMDATE") >= 0 %then %do;

51        +

52        +   %if %length(&where_clause) > 0 %then %let where_clause = &where_clause and;

53        +

54        +   %let where_clause = &where_clause (termdate between "&TERMDATE_min"d and "&TERMDATE_max"d);

55        +

56        +%end;

57        +

58        +

59        +%if %length(&NOS) >= 0 %then %do;

60        +

61        +   %if %length(&where_clause) > 0 %then %let where_clause = &where_clause and;

62        +

63        +   %let where_clause = &where_clause (NOS = &NOS);

64        +

65        +%end;

66        +

67        +%if %length(&DISP) >= 0 %then %do;

68        +

69        +   %if %length(&where_clause) > 0 %then %let where_clause = &where_clause and;

70        +

71        +   %let where_clause = &where_clause (DISP = &DISP);

72        +

73        +%end;

74        +

                                                                                          The SAS System

75        +%if %length(&where_clause) > 0 %then %do;

76        +

77        +   where &where_clause;

78        +

79        +%end;

80        +

81        +run;

82        +%mend;

83        +%data

84        +proc print data=work.test;

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference FILEDATE not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference TERMDATE not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference NOS not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference NOS not resolved.

WARNING: Apparent symbolic reference DISP not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference NOS not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference NOS not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference NOS not resolved.

WARNING: Apparent symbolic reference DISP not resolved.

WARNING: Apparent symbolic reference DISTRICT not resolved.

WARNING: Apparent symbolic reference NOS not resolved.

                                                                                          The SAS System

WARNING: Apparent symbolic reference DISP not resolved.

NOTE: Line generated by the macro variable "WHERE_CLAUSE".

84          (CIRCUIT =1) and (DISTRICT = "&DISTRICT") and (DOCKET = "") and (FILEDATE between "01Jan2009"d and "31Dec2009"d) and (termdate between ""d and ""d) and (NOS = &NOS) and (DISP = &DISP)

                                                                                                                                                                           _

                                                                                                                                                                           22

                                                                                                                                                                           76

WARNING: Apparent symbolic reference DISTRICT not resolved.

ERROR: Invalid date/time/datetime constant ""d.

ERROR: Invalid date/time/datetime constant ""d.

WARNING: Apparent symbolic reference NOS not resolved.

WARNING: Apparent symbolic reference DISP not resolved.

ERROR: Syntax error while parsing WHERE clause.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, INPUT, PUT. 

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: SAS set option OBS=0 and will continue to check statements. This may cause NOTE: No observations in data set.

WARNING: The data set WORK.TEST may be incomplete.  When this step was stopped there were 0 observations and 47 variables.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

     

85        +run;

NOTE: PROCEDURE PRINT used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

     

86        +%stpend;

NOTE: %INCLUDE (level 1) ending.


Accepted Solutions
Solution
‎02-24-2012 01:25 PM
Contributor
Posts: 48

Macro variable "where_clause"

For those of you that are curious as to what the correct answer was I'm including it here. Thank you again for everyone who participated in this community effort! Smiley Happy

libname IDB "/res/IDB/updates";

run;

Options mprint;

%macro data();

data work.test;

set idb.cv00on;

where (tapeyear ne 0)

%if &CIRCUIT_COUNT >= 0 %then %do;

    AND CIRCUIT IN

        (

            %DO I = 1 %TO &CIRCUIT_COUNT;

                %if &i=1 %then %do;

               &CIRCUIT

                %end;

                %else %do;

                &&CIRCUIT&I

                %end;

             %END;

        )

%END;

%if &DISTRICT_COUNT > 0 %then %do;

    AND trim(left(DISTRICT)) IN

        (

            %DO I = 1 %TO &DISTRICT_COUNT;

                %if &i=1 %then %do;

                "%trim(%left(&DISTRICT))"

                %end;

                %else %do;

                "%trim(%left(&&DISTRICT&I))"

                %end;

             %END;

        )

%END;

%if %length(&DOCKET) > 0 %then %do;

    and DOCKET = "&DOCKET"

%end;

%if %length(&FILEDATE_min) > 0 %then %do;

and FILEDATE between "&filedate_min"d and "&filedate_max"d

%end;

%if %length(&termDATE_min) > 0 %then %do;

and TERMDATE between "&termdate_min"d and "&termdate_max"d

%end;

%if &NOS_COUNT > 0 %then %do;

    AND NOS IN

        (

            %DO I = 1 %TO &NOS_COUNT;

                %if &i=1 %then %do;

               &nos

                %end;

                %else %do;

                &&nos&i

                %end;

             %END;

        )

%END;

%if &DISP_COUNT > 0 %then %do;

    AND DISP IN

        (

            %DO I = 1 %TO &DISP_COUNT;

                %if &i=1 %then %do;

               &disp

                %end;

                %else %do;

                &&disp&i

                %end;

             %END;

        )

%END;

;

run;

%mend;

%data

*ProcessBody;

%stpbegin;

proc print data=work.test;

run;

%stpend;

View solution in original post


All Replies
PROC Star
Posts: 7,363

Macro variable "where_clause"

Where are &district and &disp assigned?  I don't see them in your code.

Contributor
Posts: 48

Macro variable "where_clause"

&District is assigned in lines 23-29 and &DISP is assigned in lines 67-73.

PROC Star
Posts: 7,363

Macro variable "where_clause"

I don't think so.  They are referred to as existing in those lines.' I don't see where they are assigned with either let or call symput or sql statements.

Contributor
Posts: 48

Re: Macro variable "where_clause"

Why would only &district and &disp not be assigned if the if then statements are following the same structure?

PROC Star
Posts: 7,363

Re: Macro variable "where_clause"

I've never worked with storred processes before but, at the start of your program, you list a number of "SAS Macro variables assigned".  Those don't include &district or &disp

Contributor
Posts: 48

Re: Macro variable "where_clause"

Okay, yes, you are talking about the top of the program. The reason you see DISPOSITION_COUNT and DISTRICT_COUNT instead of DISPOSITION= and DISTRICT= is because these variables are multi value input parameters.; thus, _COUNT. You'll notice the same thing for NOS_COUNT. CIRCUIT was the only multi value input parameter assigned because that's the value I selected in the stored process, the others I intentionally left blank. In other words, those values should be recognized as _NULL_ but here the code doesn't seem to recognize that; only when the user specifies the value for a variable should it be overwritten, as with the case of Circuit and filedate_max and filedate_min.

CIRCUIT=1

CIRCUIT_COUNT=1

DISPOSITION_COUNT=0

DISTRICT_COUNT=0

DOCKET=

FILEDATE_MAX=31Dec2009

FILEDATE_MAX_LABEL=December 31, 2009

FILEDATE_MIN=01Jan2009

FILEDATE_MIN_LABEL=January 01, 2009

NOS_COUNT=0

TERMDATE_MAX=

TERMDATE_MIN=

PROC Star
Posts: 7,363

Re: Macro variable "where_clause"

I will have to leave this for someone who is more familiar with stored processes.  However, for &district, &disp and &nos the error messages lead me to believe that they have never been assigned.

In base SAS, one cannot refer to macro variables until after they have been resolved.  From the error messages in your log, I would have to think that the same thing is true within a storred process.


Contributor
Posts: 48

Re: Macro variable "where_clause"

Thank you Art!

SAS Super FREQ
Posts: 8,743

Re: Macro variable "where_clause"

Hi:

  Art is exactly correct. &DISTRICT is ONLY in the GLOBAL symbol table if and only if the user has made a selection in the prompting window. Just because you use &DISTRICT in your macro program, does not automatically guarantee that &DISTRICT will be available to be tested when the stored process is run.

  In our stored process class, we discuss that this is the reason for using the %GLOBAL statement in order to make sure that an "empty" macro variable is created -- therefore, it will be in the global symbol table and if the end user doesn't pick anything, then it will have a "NULL" value.

  Generally, when you make a stored process with EG, you can tell EG to add the %GLOBAL statement and the %stpbegin. If, however, you built your stored process outside of EG, then you usually have to put your own %GLOBAL statement in the code. I recommend a program flow like this. %STPBEGIN is itself a call to a macro program -- as a best practice, I rarely (very, very rarely) define a macro program while INSIDE another macro program. :

 

*ProcessBody;

%global CIRCUIT CIRCUIT_COUNT DISPOSITION_COUNT DISTRICT_COUNT

        DOCKET FILEDATE_MAX FILEDATE_MAX_LABEL FILEDATE_MIN

        FILEDATE_MIN_LABEL NOS_COUNT TERMDATE_MAX TERMDATE_MIN;

    

%macro data;

... define your macro program -outside- of %stpbegin/%stpend;

%mend data;

     

%stpbegin;

....more code like options or title statements...

  

%data;   /* invoke macro program */

 

...more code like a proc print, proc means, etc...

  

%stpend;

And, the rule of thumb is that you start with a working SAS program before you get macro programing involved. So, if you have correct WHERE statements for EVERY condition, you know how to write your macro program -- I'd recommend writing and testing that program with %IF statements outside of a stored process until you can generate each of your separate reports correctly and you know that the WHERE statements are being generated correctly.

  

THEN, and only then, create your stored process. (The SAS Lev1 directory on your server has a location where you can store AUTOCALL macro programs after they have been defined and tested. That means you would NOT have a %MACRO/%MEND section in your stored process at all.)

However you do it, I'd recommend stepping back from your long complicated stored process and alter your macro program to only generate 1 WHERE statement -- get that working. Then modify the program to generate a second WHERE statement. Then test that program with 2 %IF statements. Then once that is working, add code to generate a third WHERE statement. I'd actually recommend doing this outside of a stored process server -- because testing the code on a smaller version of the data  might be easier. But I don't know your environment or how hard that would be for you.

You could also work with Tech Support. Either way -- whether you work with Tech Support or stick with asking for help on the forums, I would recommend that you create a "cheat sheet" that shows the possible combinations of macro variables or prompts and show an example of the working where clause you want to create...something like this:

  

1) have value for &DISTRICT and have value for &CIRCUIT

   want this WHERE clause:

2) have value for &FILEDATE_MAX and &FILEDATE_MIN

   want this WHERE clause:

cynthia

Contributor
Posts: 48

Re: Macro variable "where_clause"

Thank you Cynthia. I will give that a shot.

Super Contributor
Posts: 349

Re: Macro variable "where_clause"

Hi,

Hope these two variables are inputs parameters for stored process..If yes, then need to write one more condition for missing inputs 

for eg:

%if %length(&DISTRICT) >= 0 %then %do;

%if %length(&where_clause) > 0 %then %let where_clause = &where_clause and;

%let where_clause = &where_clause (DISTRICT = "&DISTRICT");

%else %let where_clause = &where_clause and;

%let where_clause = &where_clause;

%end;

Contributor
Posts: 48

Re: Macro variable "where_clause"

Hi Shivas,


Yes, these are input parameters for the stored process. However, when I use the condition above I get the following errors:

8         +%macro data();

9         +data work.test;

10        +set idb.cv00on;

11        +%local where_clause;

12        +

13        +%let where_clause= ;

14        +

15        +%if %length(&DISTRICT) >= 0 %then %do;

16        +

17        +%if %length(&where_clause) > 0 %then %let where_clause = &where_clause and;

18        +

19        +%let where_clause = &where_clause (DISTRICT = "&DISTRICT");

ERROR: There is no matching %IF statement for the %ELSE. A dummy macro will be compiled.

20        +

21        +

22        +%else %let where_clause = &where_clause and;

23        +

24        +%let where_clause = &where_clause;

25        +

26        +

27        +%end;

28        +

29        +

30        +%if %length(&where_clause) > 0 %then %do;

31        +

32        +   where &where_clause;

33        +

34        +%end;

35        +

                                                                                          The SAS System

36        +run;

WARNING: Apparent invocation of macro DATA not resolved.

37        +%mend;

38        +%data

           _

           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

39        +proc print data=work.test;

40        +run;

41        +%stpend;

NOTE: %INCLUDE (level 1) ending.

Regular Contributor
Regular Contributor
Posts: 166

Re: Macro variable "where_clause"

Hi,

Can you try and see if something like this works...

Options mprint;

%macro data();

data work.test;

set idb.cv00on;

WHERE _N_ > 0

%if &CIRCUIT_COUNT >= 0 %then %do;

    AND trim(left(CIRCUIT)) IN

        (

            %DO I = 1 %TO &CIRCUIT_COUNT;

                %if &i=1 %then %do;.

                "%trim(%left(&CIRCUIT))"

                %end;

                %else %do;

                "%trim(%left(&&CIRCUIT&I))"

                %end;

             %END;

        )

%END;

%if &DISTRICT_COUNT >= 0 %then %do;

    AND trim(left(DISTRICT)) IN

        (

            %DO I = 1 %TO &DISTRICT_COUNT;

                %if &i=1 %then %do;.

                "%trim(%left(&DISTRICT))"

                %end;

                %else %do;

                "%trim(%left(&&DISTRICT&I))"

                %end;

             %END;

        )

%END;

%if %length(&DOCKET) >= 0 %then %do;

    and DOCKET = "&DOCKET"

%end;

%if %length(&FILEDATE_min) >= 0 %then %do;

and FILEDATE >= "&FILEDATE_min"d

%put a

%end;

%if %length(&FILEDATE_MAX) >= 0 %then %do;

and FILEDATE <= "&FILEDATE_max"d

%end;

%if %length(&termDATE_min) >= 0 %then %do;

and termDATE >= "&FILEDATE_min"d

%put a

%end;

%if %length(&termDATE_MAX) >= 0 %then %do;

and termDATE <= "&FILEDATE_max"d

%end;

%if &DISTRICT_COUNT >= 0 %then %do;

    AND trim(left(NOS)) IN

        (

            %DO I = 1 %TO &NOS_COUNT;

                %if &i=1 %then %do;.

                "%trim(%left(&NOS))"

                %end;

                %else %do;

                "%trim(%left(&&NOS&I))"

                %end;

             %END;

        )

%END;

%if &disposition_COUNT >= 0 %then %do;

    AND disposition IN

        (

            %DO I = 1 %TO &disposition_COUNT;

                %if &i=1 %then %do;.

                &disposition

                %end;

                %else %do;

                ,&&disposition&I

                %end;

             %END;

        )

%END;

;

run;

%mend;

%data

Contributor
Posts: 48

Re: Macro variable "where_clause"

Good morning NN,

When I run the code you'd suggested, I get the following error messages below. Also, with filedate and termdate those should appear as date ranges. So for filedate, we would like filedate between "&filedate_min"d and "&filedate_max"d and then termdate between "&termdate_min"d and "&termdate_max"d.

Stored Process Error

This request completed with errors.

SAS Log

1                                                                                                                        The SAS System                                                                                          07:21 Friday, February 24, 2012

NOTE: Copyright (c) 2002-2008 by SAS Institute Inc., Cary, NC, USA.

NOTE: SAS (r) Proprietary Software 9.2 (TS2M3)

      Licensed to FEDERAL JUDICIAL CENTER, Site 70021403.

NOTE: This session is executing on the X64_SRV08  platform.

NOTE: SAS Initialization used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

     

NOTE: The autoexec file, C:\SAS\AppDev\Lev1\SASApp\StoredProcessServer\autoexec.sas, was executed at server initialization. No server log was specified.  Add the log option to the server startup command to see details of the autoexec execution (refer to

      "Specifying Logging Options" under "Server Startup Command" in the Server Administrator's Guide).

>>> SAS Macro Variables:

CIRCUIT=1

CIRCUIT_COUNT=1

DISPOSITION_COUNT=0

DISTRICT=01

DISTRICT_COUNT=1

DOCKET=

FILEDATE_MAX=31Dec2009

FILEDATE_MAX_LABEL=December 31, 2009

FILEDATE_MIN=01Jan2009

FILEDATE_MIN_LABEL=January 01, 2009

NOS=130

NOS_COUNT=1

TERMDATE_MAX=

TERMDATE_MIN=

_APSLIST=Circuit,Circuit_count,District,District_count,Docket,Filedate_max,Filedate_max_label,Filedate_min,Filedate_min_label,Termdate_max,Termdate_min,NOS,NOS_count,Disposition_count,_odsdest,_debug,_srvport,_grafloc,_srvname,_reqmeth,_htcook,_htua,_url,

     _version,_rmthost,_program,_username,_rmtaddr,_result,_metaperson,_metauser,_metafolder,_client,_SECUREUSERNAME

_CLIENT=StoredProcessService 9.2; JVM 1.5.0_15; Windows Vista (amd64) 6.1

_DEBUG=log

_GRAFLOC=/sasweb/graph

_HTCOOK=JSESSIONID=5BB6FEC2226052076BA15809BE23CACC

_HTUA=Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; GTB7.2; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729)

_METAFOLDER=/Users/FJC Research/IDB/

_METAPERSON=webanon

_METAUSER=webanon@saspw

_ODSDEST=HTML

_PROGRAM=/Users/FJC Research/IDB/IDB Civil

_REPLAY="&_URL?_sessionid=E8B26711-BECE-4CA4-839E-865F0601A126&_program=replay&_entry=&_TMPCAT.."

_REQMETH=GET

_RESULT=STREAM

_RMTADDR=169.254.95.120

_RMTHOST=169.254.95.120

_SECUREUSERNAME=webanon

_SRVNAME=fjcsas.ad.fjc.dcn

_SRVPORT=8080

_TMPCAT=APSWORK.TCAT01A2

_URL=/SASStoredProcess/guest

_USERNAME=webanon@saspw

_VERSION=Version 9.2 (Build 420)

1          options nosource source2 center notes nodate nonumber ls=195 formchar='|----|+|---+=|-/\<>*' pagesize=40 noovp nomprint nomlogic nosymbolgen; title; footnote;

                                                                                          The SAS System

NOTE: %INCLUDE (level 1) file C:\Users\amason\Stored\IDBCivil.sas is file C:\Users\amason\Stored\IDBCivil.sas.

3         +libname IDB "/res/IDB/updates";

NOTE: Libref IDB was successfully assigned as follows:

      Engine:        V9

      Physical Name: C:\res\IDB\updates

4         +run;

5         +

6         +Options mprint;

7         +

8         +%macro data();

9         +

10        +data work.test;

11        +

12        +set idb.cv00on;

13        +

14        +WHERE _N_ > 0

15        +

16        +

17        +%if &CIRCUIT_COUNT >= 0 %then %do;

18        +

19        +    AND trim(left(CIRCUIT)) IN

20        +

21        +        (

22        +

23        +            %DO I = 1 %TO &CIRCUIT_COUNT;

24        +

25        +                %if &i=1 %then %do;.

26        +

27        +                "%trim(%left(&CIRCUIT))"

28        +

29        +                %end;

30        +

31        +                %else %do;

32        +

33        +                "%trim(%left(&&CIRCUIT&I))"

34        +

35        +                %end;

36        +

                                                                                          The SAS System

37        +             %END;

38        +

39        +        )

40        +

41        +%END;

42        +

43        +

44        +%if &DISTRICT_COUNT >= 0 %then %do;

45        +

46        +    AND trim(left(DISTRICT)) IN

47        +

48        +        (

49        +

50        +            %DO I = 1 %TO &DISTRICT_COUNT;

51        +

52        +                %if &i=1 %then %do;.

53        +

54        +                "%trim(%left(&DISTRICT))"

55        +

56        +                %end;

57        +

58        +                %else %do;

59        +

60        +                "%trim(%left(&&DISTRICT&I))"

61        +

62        +                %end;

63        +

64        +             %END;

65        +

66        +        )

67        +

68        +%END;

69        +

70        +

71        +%if %length(&DOCKET) >= 0 %then %do;

72        +

73        +    and DOCKET = "&DOCKET"

74        +

                                                                                          The SAS System

75        +%end;

76        +

77        +

78        +%if %length(&FILEDATE_min) >= 0 %then %do;

79        +

ERROR: Macro keyword END appears as text.  A semicolon or other delimiter may be missing.

80        +and FILEDATE >= "&FILEDATE_min"d

81        +

82        +%put a

83        +

84        +%end;

85        +

86        +%if %length(&FILEDATE_MAX) >= 0 %then %do;

87        +

88        +and FILEDATE <= "&FILEDATE_max"d

89        +

90        +%end;

91        +

92        +

93        +%if %length(&termDATE_min) >= 0 %then %do;

94        +

ERROR: Macro keyword END appears as text.  A semicolon or other delimiter may be missing.

95        +and termDATE >= "&FILEDATE_min"d

96        +

97        +%put a

98        +

99        +%end;

100       +

101       +%if %length(&termDATE_MAX) >= 0 %then %do;

102       +

103       +and termDATE <= "&FILEDATE_max"d

104       +

105       +%end;

106       +

107       +

108       +%if &DISTRICT_COUNT >= 0 %then %do;

109       +

110       +    AND trim(left(NOS)) IN

                                                                                          The SAS System

111       +

112       +        (

113       +

114       +            %DO I = 1 %TO &NOS_COUNT;

115       +

116       +                %if &i=1 %then %do;.

117       +

118       +                "%trim(%left(&NOS))"

119       +

120       +                %end;

121       +

122       +                %else %do;

123       +

124       +                "%trim(%left(&&NOS&I))"

125       +

126       +                %end;

127       +

128       +             %END;

129       +

130       +        )

131       +

132       +%END;

133       +

134       +

135       +

136       +%if &disposition_COUNT >= 0 %then %do;

137       +

138       +    AND disposition IN

139       +

140       +        (

141       +

142       +            %DO I = 1 %TO &disposition_COUNT;

143       +

144       +                %if &i=1 %then %do;.

145       +

146       +                &disposition

147       +

148       +                %end;

                                                                                          The SAS System

149       +

150       +                %else %do;

151       +

152       +                ,&&disposition&I

153       +

154       +                %end;

155       +

156       +             %END;

157       +

158       +        )

159       +

160       +%END;

161       +

162       +;

ERROR: There were 2 unclosed %DO statements.  The macro DATA will not be compiled.

163       +

164       +run;

WARNING: Apparent invocation of macro DATA not resolved.

165       +

166       +%mend;

167       +

168       +%data

           _

           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 44 replies
  • 1117 views
  • 1 like
  • 7 in conversation