Where vs. If Statement Examples in usage and differences



Overdrive with DO_OVER macro

Sunil Gupta

Gupta Programming

Set up to access files: See Model Do Over.sas

filename mspmac "C:\SAS\Production SAS Code\Utility Macros";

options msglevel = i mautosource sasautos=(sasautos mspmac) mprint;

| | |

|DO_OVER Macro Call |SAS Log: Resolved Macro |

|* Build short lists, without commas in PUT statement; |* Build PUT statement; |

| | |

|Data _null_; |Data _null_; |

|put "This is a test: |put "This is a test: pre_A pre_B pre_C " ; |

|%DO_OVER(VALUES=A B C, PHRASE=pre_?) |run; |

|" ; | |

|run; | |

|* Build short lists in LENGTH statement; |* Build LENGTH and assignment statements; |

| | |

|Data valueds; |Data valueds; |

| |Length pre_A pre_B pre_C 8; |

|length %DO_OVER(VALUES=A B C, PHRASE=pre_?) 8; |pre_A=3; |

| |pre_B=3; |

|%DO_OVER(VALUES=A B C, PHRASE=pre_?=3;); |pre_C=3; |

|run; |; |

| |run; |

|* Build short lists as array to be used multiple times; |* Build LENGTH and assignment statements; |

| | |

|%array(varnm, values=A B C); | |

| |Data valueds; |

|Data arrayonly; |Length pre_A pre_B pre_C 8; |

| |pre_A=3; |

|length %DO_OVER(varnm, PHRASE=pre_?) 8; |pre_B=3; |

| |pre_C=3; |

|%DO_OVER(varnm, PHRASE=pre_?=3;) |; |

|run; |run; |

| | |

|proc print data=arrayonly; |proc print data=arrayonly; |

|var %DO_OVER(varnm, PHRASE=pre_?) ; |var pre_A pre_B pre_C; |

|run; |run; |

|* Build long lists as data set; |* Build LENGTH and assignment statements; |

| | |

|data araylst; |data araylst; |

|length varnm2 $15; |length varnm2 $15; |

|varnm2 = 'A'; output; |varnm2 = 'A'; output; |

|varnm2 = 'B'; output; |varnm2 = 'B'; output; |

|varnm2 = 'C'; output; |varnm2 = 'C'; output; |

|run; |run; |

|proc print data=araylst; run; |proc print data=araylst; run; |

| | |

|%array(array=varnm3, data=araylst, var=varnm2); |Data valueds; |

| |Length pre_A pre_B pre_C 8; |

|Data arrayds; |pre_A=3; |

| |pre_B=3; |

|length %DO_OVER(varnm3, PHRASE=pre_?) 8; |pre_C=3; |

| |; |

|%DO_OVER(varnm3, PHRASE=pre_?=3;) |run; |

|run; | |

|* Build short lists using starting and ending values as array to be used |* Build LENGTH and assignment statements; |

|multiple times; | |

| | |

|%array(varnm, values=D_M1-D_M3); |Data valueds; |

| |Length pre_D_M1 pre_D_M2 pre_D_M3 8; |

|Data arrayonly; |pre_D_M1=3; |

| |pre_D_M2=3; |

|length %DO_OVER(varnm, PHRASE=pre_?) 8; |pre_D_M3=3; |

| |; |

|%DO_OVER(varnm, PHRASE=pre_?=3;) |run; |

|run; | |

| |proc print data=arrayonly; |

|proc print data=arrayonly; |var pre_D_M1 pre_D_M2 pre_D_M3; |

|var %DO_OVER(varnm, PHRASE=pre_?) ; |run; |

|run; | |

|* Quoted list in SAS statement; |* Build IF statement; |

| | |

|data test; |data test; |

|length name $10; |length name $10; |

|name = 'Bill'; output; |name = 'Bill'; output; |

|name = 'Sue'; output; |name = 'Sue'; output; |

|name = 'Tom'; output; |name = 'Tom'; output; |

|run; |run; |

| | |

|Data myname; |Data myname; |

|set test; |set test; |

|if name in (%DO_OVER(VALUES=Bill Sam, PHRASE="?")); |if name in ("Bill" "Sam"); |

|run; |run; |

|* Build list of month variables and first of month dates; |* Build series of IF THEN statements; |

| | |

|%array(mvarnm, values= D_M1-D_M12); |if monthdt='01jan2011'd then d_m1=1; |

| | |

|%array(ndtvarnm, values= '01jan2011'd '01feb2011'd '01mar2011'd '01apr2011'd |… |

|'01may2011'd '01jun2011'd '01jul2011'd '01aug2011'd '01sep2011'd '01oct2011'd | |

|'01nov2011'd '01dec2011'd); |Else if monthdt='01dec2011'd then d_m12=1; |

| | |

|%DO_OVER(ndtvarnm mvarnm, PHRASE=if monthdt=?ndtvarnm then ?mvarnm=1;, | |

|between=else) | |

|* Build short list of excel file names; |* Automatic calling of proc import for each excel file; |

| | |

|%do_over(values= 00_01_00_Category Code(e).xls|00_02_00_Channel Code(e).xls| |proc import datafile= "&maps.\ 00_01_00_Category Code(e).xls" |

|00_04_00_Manufacturer Code(e).xls|00_05_00_Brand Code(e).xls| 00_06_00_Subject |out= map00_01_00_Category Code(e).xls_1_ |

|Brand(e)_js.xls, |replace |

|phrase= |dbms= excel;, |

|proc import datafile= "&maps.\?" |      delim= |; |

|out= map?_i_ |run; |

|replace | |

|dbms= excel;, |(Repeat for each excel file) |

|      delim= |; | |

|) | |

|run; | |

|* Subset metadata to build list; |* Build list from metadata; |

| | |

|%array(array=_vars2, data= sashelp.vcolumn (where=(libname='WORK' and memname= |Data vcolumn; |

|'VSET2' and name not in ('date','month'))), var=name) |Set sashelp.vcolumn; |

| |Where libname='WORK' and memname= 'VSET2' and name not in ('date','month'); |

| |Run; |

| | |

| |%array(array=_vars2, data= vcolumn, var=name) |

|* Tracking merged data sets before keyword and with (in=); |* Build MERGE statement; |

| | |

|merge %DO_OVER(VALUES=A B, PHRASE=?(IN?))); |merge A(IN=INA) B(IN=INB); |

|* Array index number in MERGE statement; |* Build MERGE statement; |

| | |

|%DO_OVER(VALUES=A B, PHRASE=merge ?(IN=IN?_I_)); |merge A(IN=IN1) B(IN=IN2); |

|* Complete statements within a SAS Procedure such as Proc Freq; |* Build PROC FREQ statements; |

| | |

|proc freq; |proc freq; |

|%DO_OVER(VALUES=A B, PHRASE=TABLE ? / OUT=FREQS?;); |TABLE A / OUT=FREQSA; |

|Run; |TABLE B / OUT=FREQSB; |

| |Run; |

|* Sorting multiple data sets; |* Automatic sorting of multiple data sets; |

| | |

|%array(_mfbrwk, values= stack2 t.kao_survey t.kao_promo); |proc sort data= stack2; |

| |by att_mfg_brnd dt_week; |

|%do_over(_mfbrwk, |run; |

|phrase= proc sort data=?; |proc sort data= t.kao_survey; |

|  by att_mfg_brnd dt_week;) |by att_mfg_brnd dt_week; |

|run; |run; |

| |proc sort data= t.kao_promo; |

| |by att_mfg_brnd dt_week; |

| |run; |

|* Multiple statements without the need to create traditional macros; |* Build PROC PRINT statements; |

| | |

|%DO_OVER(VALUES=A B, PHRASE=TITLE "PRINTOUT OF ?";PROC PRINT DATA = ?;); |TITLE "PRINTOUT OF A"; |

| |PROC PRINT DATA = A; |

| |TITLE "PRINTOUT OF B"; |

| |PROC PRINT DATA = B; |

|* Macro Language for dynamic lists instead of hard coding values; |* Build macro statements; |

| | |

|%let old=A B; |%let old=A B; |

|%let new=%do_over(values=&old, phrase=pct_?); |%let new=PCT_A PCT_B; |

|* Inserting text such as else or commas between values; |* Build IF THEN statements; |

| | |

|%DO_OVER(VALUES=A B, PHRASE=if letter="?" then ?=1;, between=else); |IF LETTER="A" THEN A=1; |

| |ELSE IF LETTER="B" THEN B=1; |

|* Processing two or more lists; |* Build RENAME statement; |

| | |

|%ARRAY(AB, A B); |rename A=C B=D; |

|%ARRAY(CD, C D); | |

|rename %DO_OVER(AB CD, PHRASE=?AB=?CD); | |

|* Calling macros with multiple lists of macro parameters; |* Automatic calling of any positional parameter macros; |

| | |

|%ARRAY(AB, A B); |%DOIT(A, C); |

|%ARRAY(CD, C D); |%DOIT(B, D); |

|%DO_OVER(AB CD, MACRO=DOIT); | |

|* Nested DO_OVER macro call; |* Automatically creating data sets with multiple renames; |

| | |

|%array(_prptmet1, values= yen units) |data pr2_ yen(drop= _:); |

|%array(_prptmet2, values= amt_by_channel_yen units_by_channel) |set pr2(rename=( _2005=yen_2005 _2006=yen_2006 _2007=yen_2007 _2008=yen_2008 |

| |_2009=yen_2009 _2010=yen_2010) |

|%do_over(_prptmet1 _prptmet2, |  where=(_name_= "amt_by_channel_yen ")); |

|phrase=data pr2_?_prptmet1(drop= _:); |run; |

|set pr2(rename= (%do_over(values= 2005-2010, | |

|phrase= _?= yen_?)) |data pr2_ units(drop= _:); |

|   where= ( _name_= "?_prptmet2")); |set pr2(rename=( _2005= units_2005 _2006= units_2006 _2007= units_2007 _2008= |

|) |units_2008 _2009= units_2009 _2010= units_2010) |

|run; |  where=(_name_= "units_by_channel ")); |

| |run; |

|* Use %ARRAY with %DO_OVER to access macro array variables directly; |(To Be Added) |

| | |

|%* Store macro abbreviation; | |

|%let _mac=dv; | |

|  | |

|%* Store user options; | |

|%array( _&_mac.opt, values=mprint mlogic xwait xsync symbolgen) | |

|%do _cnt= 1 %to &&_&_mac.optN; | |

| %let _&&_&_mac.opt&_cnt= %sysfunc( getoption( &&_&_mac.opt&_cnt, | |

|keyword) ) ; | |

|  %end; | |

|  | |

|* Set macro options; | |

| options mprint nomlogic noxwait xsync nosymbolgen; | |

|  | |

|%* Reset user options; | |

|  options | |

|    %do _cnt= 1 %to &&_&_mac.optN; | |

|      &&&&_&&_&_mac.opt&_cnt... | |

|    %end; | |

|; | |

|* Call %VARLISTN within %DO_OVER; |(To Be Added) |

| | |

|%Do_over(Values= %varlistn(ls.lexus_all), | |

|Phrase = if ?=. then ?=0; | |

|) | |

|* do_over macro called with proc sql and comma reserved keyword; |* Build list of columns; |

| | |

|%array(array=_vars2, data= sashelp.vcolumn |%array(array=_vars2, data= sashelp.vcolumn |

|(where=(libname='SQLTST3' and memname = 'MONTHLY' and name not in |(where=(libname='SQLTST3' and memname = 'MONTHLY' and name not in |

|('date','month'))), var=name); |('date','month'))), var=name); |

| | |

|proc sql; |proc sql; |

|create table monthly4_all as |create table monthly4_all as |

|select month, week_id, weekcnt, date, lmonth, |select month, week_id, weekcnt, date, lmonth, |

|%do_over(_vars2, phrase= (?/weekcnt) as ?, between=COMMA) | |

|from monthly3 |(BROADCAST_CO_BRD/weekcnt) as BROADCAST_CO_BRD , (PRINT_CO_BRD/weekcnt) as |

|group by date, week_id; |PRINT_CO_BRD , (INTERACTIVE_CO_BRD/weekcnt) as INTERACTIVE_CO_BRD , |

|quit; |(OOH_CO_BRD/weekcnt) as |

| |OOH_CO_BRD , (EM_CO_BRD/weekcnt) as EM_CO_BRD , (BROADCAST_Brand_Oct/weekcnt) |

| |as |

| |BROADCAST_Brand_Oct , (PRINT_Brand_Oct/weekcnt) as PRINT_Brand_Oct , |

| |(INTERACTIVE_Brand_Oct/weekcnt) as INTERACTIVE_Brand_Oct , |

| |(OOH_Brand_Oct/weekcnt) as |

| |OOH_Brand_Oct , (EM_Brand_Oct/weekcnt) as EM_Brand_Oct , |

| |(BROADCAST_C_Premium/weekcnt) as |

| |BROADCAST_C_Premium , (PRINT_C_Premium/weekcnt) as PRINT_C_Premium , |

| |(INTERACTIVE_C_Premium/weekcnt) as INTERACTIVE_C_Premium , |

| |(OOH_C_Premium/weekcnt) as |

| |OOH_C_Premium , (EM_C_Premium/weekcnt) as EM_C_Premium , |

| |(BROADCAST_Hybrid_BRD/weekcnt) as |

| |BROADCAST_Hybrid_BRD , (PRINT_Hybrid_BRD/weekcnt) as PRINT_Hybrid_BRD , |

| |(INTERACTIVE_Hybrid_BRD/weekcnt) as INTERACTIVE_Hybrid_BRD , |

| |(OOH_Hybrid_BRD/weekcnt) as |

| |OOH_Hybrid_BRD , (EM_Hybrid_BRD/weekcnt) as EM_Hybrid_BRD , |

| |(BROADCAST_restore_brd/weekcnt) as |

| |BROADCAST_restore_brd , (PRINT_restore_brd/weekcnt) as PRINT_restore_brd , |

| |(INTERACTIVE_restore_brd/weekcnt) as INTERACTIVE_restore_brd , |

| |(OOH_restore_brd/weekcnt) as |

| |OOH_restore_brd , (EM_restore_brd/weekcnt) as EM_restore_brd |

| | |

| |from monthly3 |

| |group by date, week_id; |

| |quit; |

|* Starting and ending dates work around - follow 4 step process; |* Hard coding of starting and ending dates as first of each month; |

| | |

|* Step 1. Create data set of dates from starting and ending dates; | |

|* Examples - first of each month or every Mondays for example; |%array(dtvarnm, values= '01jan2010'd '01feb2010'd '01mar2010'd '01apr2010'd |

| |'01may2010'd '01jun2010'd '01jul2010'd '01aug2010'd '01sep2010'd '01oct2010'd |

|data wdates; |'01nov2010'd '01dec2010'd); |

|format weekbeg date9.; | |

|do weekbeg='01jan2010'd to '31dec2010'd by 1; |* Simple example to create records where weekbeg equals each date; |

|if day(weekbeg) = 1 then output; | |

|end; |data arraytst; |

|run; |length weekbeg 8; |

| |format weekbeg date9.; |

|* Step 2. Create sedate macro variable of months from wdates data set; | |

|* Can apply where condition to select only first of each month or every Mondays|%DO_OVER(dtvarnm, PHRASE=weekbeg = ?; output;) |

|for example; | |

| |run; |

|proc sql; | |

|select "'" !! trim(put(weekbeg, date9.)) !! "'d" into: sedate separated by ' ' | |

|from wdates; | |

|quit; | |

|%put &sedate; | |

| | |

|* Step 3. Use sedate macro variable in array macro call; | |

| | |

|%array(dtvarnm, values= &sedate); | |

|* Create macro variable for test purpose; | |

|%*let sedate = %str('01jan2010'd '01dec2010'd); | |

| | |

|* Step 4. Apply %do_over macro using array of starting and ending months; | |

| | |

|data arraytst; | |

|length weekbeg 8; | |

|format weekbeg date9.; | |

|%DO_OVER(dtvarnm, PHRASE=weekbeg = ?; output;) | |

|run; | |

%ARRAY SYNTAX

%ARRAY(ARRAYPOS, ARRAY=, DATA=, VAR=, VALUES=, DELIM=%str( ));

Parameter Description

|ARRAYPOS |Name(s) for the macro array(s) to be defined. (Required) |

|ARRAY |Keyword alternative to the ARRAYPOS positional parameter.. |

|DATA |Dataset or view source of text data. Data set options are allowed. If this parameter is used, the ARRAY macro must be called between |

| |other steps of the program. |

| |One or more character or numeric variable(s) containing values to put in the array(s). Required with DATA=. If multiple arrays are |

| |defined, the VAR= list must correspond onefor- one with the macro names given in the ARRAY or ARRAYPOS parameter. |

|VAR | |

| |An explicit list of character strings to put in the array. It can only be used when defining a single array. The VALUES= can be a |

| |numbered list. This can be number-hyphen-number, or with an alpha prefix like a SAS numbered variable list such as x3-x10. |

|VALUES | |

|DELIM |A single-character separator for parts of the VALUES parameter. Default=space. |

|DEBUG |Set to Y to activate debugging feature. Displays number of array elements to SAS log. |

|NUMLIST |Default to Y so that VALUES may be a number list, ex. values=D_M1-D_M3 |

%DO_OVER SYNTAX

%DO_OVER(ARRAYPOS, ARRAY=, PHRASE=?, ESCAPE=?, BETWEEN=, MACRO=,

VALUES=, DELIM=%str( ));

Parameter Description

| |Name(s) for the macro array(s) to iterate over. If multiple macro arrays are given, they must have the same length. The only |

|ARRAYPOS |positional parameter. Required if no VALUES=. |

|ARRAY |Keyword alternative to the ARRAYPOS positional parameter.. |

| |SAS code into which to substitute the macro variable values. The PHRASE parameter may contain semicolons and extend to multiple lines.|

| |The default value of PHRASE is a single . (See Example 5 for the default case.) |

| |Single Array Case : Put a “?” (the ESCAPE character) wherever you want the macro values to be placed. Multiple Array Case: Put a “?” |

| |immediately followed by the name of the macro array. The macro will replace the “?” and name with the value of that particular macro |

| |array. References to macro names are case sensitive and must agree with the ARRAY parameter values. Obtaining the Loop Index: Put |

| |“?_I_” wherever you want the value of the looping index to appear. It is replaced by “1”, “2”, “3”, etc. up to the length of the |

| |array. |

|PHRASE | |

|ESCAPE |A single character to be replaced by macro array values. Default is "?". If followed by a macro array name, it plus the array name are|

| |replaced by the array values. |

| |Code to generate between iterations of the main phrase or macro. Because there is often a need to put a comma between elements of an |

| |array, the special parameter value COMMA is recognized for programming convenience. BETWEEN=COMMA is equivalent to BETWEEN=%STR(,). |

|BETWEEN | |

| |The name of an externally-defined macro to execute on each value of the array. It |

| |overrides the PHRASE parameter. The macro must have positional parameters defined, in the same order and meaning as the macro arrays |

|MACRO |specified in the ARRAY or ARRAYPOS parameter. |

| |An explicit list of character strings to iterate over. This parameter is an alternative to giving an array name in the ARRAYPOS or |

| |ARRAY= parameter. A single hidden internal array is created and used. When VALUES= are given, the PHRASE parameter may only use |

| |single. If a MACRO= is supplied, it must be a single-parameter macro. The VALUES= can be a numbered list. This can be |

| |number-hyphen-number, or with an alpha prefix like a SAS numbered variable list such as x3-x10. |

|VALUES | |

|DELIM |A single-character separator for parts of the VALUES parameter. Default=space. |

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download