topic Re: Macro variable "where_clause" in SAS Programming

Macro variable "where_clause"

AshleyM — Thu, 23 Feb 2012 15:27:10 GMT

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;