Macro variable "where_clause"

[art297]

I can't test the code, but there definitely appears to be extra characters in NN's code.  Specifically, all of the lines that show:

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

Should not have a period at the end.  i.e., they should just read:

%if &i=1 %then %do;

Also, did you take Cynthia's suggestion and set all of your macro variables to be Global?

[Astounding]

Ashley,

Again, moving in the right direction.  Cynthia's advice was good general advice, but you won't need to define %GLOBAL variables since you already have the & _COUNT variables to work with.  (At least probably, you won't need to.)

As Art mentioned, there are some extra characters here and there.  Besides the periods after the semicolons, there are also a few %PUT A pieces to be removed.

Other factors:  &DISTRICT_COUNT is being used to control iterations for &NOS, instead of &NOS_COUNT.

Finally, the WHERE statement will not let you use _N_.  I recognize this as trying to simplify whether to add the word AND in later pieces of the WHERE statement.  I'm not sure if this will work either, but try this in a DATA step:

data new;

set old;

where 1;

run;

If it works, that would be the way to start the WHERE statement (omitting the semicolon until you reach the end of the WHERE statement, as your code currently does).  If not, you might have to select a variable that is always nonmissing and condition on that, such as:

where (district > ' ')

[AshleyM]

Thank you Art and Astounding. After tweaking the code and using the variable "tapeyear" which is always nonmissing, I get the following error message.

MPRINT(DATA):   data work.test;

MPRINT(DATA):   set idb.cv00on;

MPRINT(DATA):   where (tapeyear ne 0) AND trim(left(CIRCUIT)) IN (

MPRINT(DATA):   "1" ) AND trim(left(DISTRICT)) IN (

MPRINT(DATA):   "01" ) and FILEDATE between "01Jan2009"d and "31Dec2009"d AND trim(left(NOS)) IN (

MPRINT(DATA):   "110" ) ;

ERROR: Function LEFT requires a character expression as argument 1.

MPRINT(DATA):   run;

[art297]

In other places in your code it is enclosed in double quotes: "%trim(%left(&NOS))"

Does it work if you enclose that use of it?

[AshleyM]

In other words, you're suggesting to include this here below. If yes, when I ran the code that way it still did not work.

%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;

        )

[art297]

No!  You didn't get the error regarding Circuit, you got it regarding NOS.  I was only suggesting changing it for NOS.

And, considering Cynthia's advice regarding the no matching %if statement, it would be helpful to see your latest log after correcting the NOS statements.

However, I totally agree with Cynthia that your code would be a lot easier to read if you structured %if %then %else statements like she suggested.

[AshleyM]

Here's the log after correcting the NOS statements:

>>> 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=110

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=7CF60A79-41E6-4493-9EB5-5673CF5B877E&_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.TCAT01D5

_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 (tapeyear ne 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        +

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

81        +

82        +%end;

83        +

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

85        +

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

87        +

88        +%end;

89        +

90        +

91        +%if &NOS_COUNT > 0 %then %do;

92        +

93        +    AND "trim(left(NOS))" IN

94        +

95        +        (

96        +

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

98        +

99        +                %if &i=1 %then %do;

100       +

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

102       +

103       +                %end;

104       +

105       +                %else %do;

106       +

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

108       +

109       +                %end;

110       +

111       +             %END;

112       +

                                                                                          The SAS System

113       +        )

114       +

115       +%END;

116       +

117       +

118       +

119       +%if &disposition_COUNT > 0 %then %do;

120       +

121       +    AND disposition IN

122       +

123       +        (

124       +

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

126       +

127       +                %if &i=1 %then %do;

128       +

129       +                &disposition

130       +

131       +                %end;

132       +

133       +                %else %do;

134       +

135       +                &&disposition&I

136       +

137       +                %end;

138       +

139       +             %END;

140       +

141       +        )

142       +

143       +%END;

144       +

145       +;

146       +

147       +run;

148       +

149       +%mend;

150       +

                                                                                          The SAS System

151       +%data

152       +

153       +proc print data=work.test;

MPRINT(DATA):   data work.test;

MPRINT(DATA):   set idb.cv00on;

MPRINT(DATA):   where (tapeyear ne 0) AND trim(left(CIRCUIT)) IN (

MPRINT(DATA):   "1" ) AND trim(left(DISTRICT)) IN (

MPRINT(DATA):   "01" ) and FILEDATE between "01Jan2009"d and "31Dec2009"d AND "trim(left(NOS))" IN (

MPRINT(DATA):   "110" ) ;

ERROR: Function LEFT requires a character expression as argument 1.

MPRINT(DATA):   run;

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.01 seconds

      cpu time            0.01 seconds

154       +run;

NOTE: PROCEDURE PRINT used (Total process time):

      real time           0.02 seconds

      cpu time            0.01 seconds

NOTE: %INCLUDE (level 1) ending.

[art297]

Shouldn't

"trim(left(NOS))"

be

"trim(left(&NOS))"

???

[AshleyM]

I previously tried "trim*(left(&NOS))" and the where statement replaces NOS with the value in the trim statement. Notice, below where NOS is 110.

MPRINT(DATA):   data work.test;

MPRINT(DATA):   set idb.cv00on;

MPRINT(DATA):   where (tapeyear ne 0) AND trim(left(CIRCUIT)) IN (

MPRINT(DATA):   "1" ) AND trim(left(DISTRICT)) IN (

MPRINT(DATA):   "01" ) and FILEDATE between "01Jan2009"d and "31Dec2009"d AND trim(left(110)) IN (

MPRINT(DATA):   "110" ) ;

ERROR: Function LEFT requires a character expression as argument 1.
MPRINT(DATA):   run;

[Astounding]

Is it possible that CIRCUIT is numeric in your data?

[AshleyM]

Yes, Circuit, Filedate, Termdate, NOS and Disposition are all numeric, District and Docket are character.

[Tom]

That is an error message from the SAS code generated by your macro.  One of the dataset variables CIRCUIT, DISTRICT or NOS is numeric instead of character.  So you cannot use character function like LEFT() on a numeric variable in WHERE statement.

[art297]

Tom: I'm not sure if it is a numeric variable as I think it is a macro variable.

Ashley: While Tom may be right, the following worked in my test:

%let nos=110;

data work.test;

  set sashelp.class;

  where trim(left("&nos.")) IN ("110" ) ;

run;

[Astounding]

True. Remove both TRIM and LEFT for numeric variables.

Also, the values inside the IN operator should not have quotes around them for numeric variables.  (Althought the quotes are still needed for the date literals such as "&FILEDATE_MIN"d).

[AshleyM]

Thank you, you're suggestions worked. AND thank you to the rest of the group on the community effort. The only thing I'm not certain of now is why no output is being generated.

Stored Process Error

The SAS program did not produce any output.

SAS Log

1                                                                                                                        The SAS System                                                                                          11:38 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.01 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=0

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=110

NOS0=3

NOS1=110

NOS2=120

NOS3=130

NOS_COUNT=3

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,NOS0,NOS1,NOS2,NOS3,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=20CF176A-1AFF-4447-8A96-F7FFAFC18386&_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.TCAT01FB

_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 (tapeyear ne 0)

15        +

16        +

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

18        +

19        +    AND CIRCUIT IN

20        +

21        +        (

22        +

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

24        +

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

26        +

27        +               &CIRCUIT

28        +

29        +                %end;

30        +

31        +                %else %do;

32        +

33        +                &&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        +

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

81        +

82        +%end;

83        +

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

85        +

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

87        +

88        +%end;

89        +

90        +

91        +%if &NOS_COUNT > 0 %then %do;

92        +

93        +    AND NOS IN

94        +

95        +        (

96        +

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

98        +

99        +                %if &i=1 %then %do;

100       +

101       +               &nos

102       +

103       +                %end;

104       +

105       +                %else %do;

106       +

107       +                &&nos&i

108       +

109       +                %end;

110       +

111       +             %END;

112       +

                                                                                          The SAS System

113       +        )

114       +

115       +%END;

116       +

117       +

118       +

119       +%if &disposition_COUNT > 0 %then %do;

120       +

121       +    AND DISP IN

122       +

123       +        (

124       +

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

126       +

127       +                %if &i=1 %then %do;

128       +

129       +                &DISP

130       +

131       +                %end;

132       +

133       +                %else %do;

134       +

135       +                &&DISP&I

136       +

137       +                %end;

138       +

139       +             %END;

140       +

141       +        )

142       +

143       +%END;

144       +

145       +;

146       +

147       +run;

148       +

149       +%mend;

150       +

                                                                                          The SAS System

151       +%data

152       +

153       +proc print data=work.test;

MPRINT(DATA):   data work.test;

MPRINT(DATA):   set idb.cv00on;

MPRINT(DATA):   where (tapeyear ne 0) AND CIRCUIT IN ( 0 ) and FILEDATE between "01Jan2009"d and "31Dec2009"d AND NOS IN ( 110 120 130 ) ;

MPRINT(DATA):   run;

NOTE: There were 29 observations read from the data set IDB.CV00ON.

      WHERE (tapeyear not = 0) and (CIRCUIT=0) and (FILEDATE>='01JAN2009'D and FILEDATE<='31DEC2009'D) and NOS in (110, 120, 130);

NOTE: The data set WORK.TEST has 29 observations and 47 variables.

NOTE: DATA statement used (Total process time):

      real time           0.53 seconds

      cpu time            0.54 seconds

154       +run;

NOTE: There were 29 observations read from the data set WORK.TEST.

NOTE: The PROCEDURE PRINT printed pages 1-3.

NOTE: PROCEDURE PRINT used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

NOTE: %INCLUDE (level 1) ending.