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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- more formulae and functions exercises ucl
- random numbers and stochastic simulations in excel
- exercise in input output analysis a manual compilation
- software requirements specification document template
- advanced excel statistical functions formulae
- this web page contains material for the computing an data
- competency examples with performance statements
- where vs if statement examples in usage and differences
Related searches
- count if statement in tableau
- nested if statement in tableau
- if statement in tableau calculated field
- verilog if statement in case
- financial statement examples in excel
- closing statement examples in speech
- if statement in python example
- if statement with and condition
- 2 condition if statement in excel
- if statement in excel with 3 conditions
- if statement in excel with and
- if statement in excel with multiple words