How to specify the where clause in the DI Studio version of PROC FREQ

[Anne_A]

Hi all,

I'm trying to specify a WHERE clause in the DI Studio version of PROC FREQ: One-Way Frequency. Screenshots below show where I've entered my info, and the corresponding part of the SAS log.

Is it possible to specify a WHERE clause within the One-Way Frequency transformation, or do I have to use an extract transformation beforehand?

Many thanks in advance

Cheers

Anne.

[LinusH]

That's weird, where dataset option should be supported.

Can you attach the whole log for that step?

But yes, a work-around could be to use an Extract instead.

[Anne_A]

Hi @LinusH,

Here's the full log. Thanks for taking an interest in my question!

Cheers

Anne.

51                                                         The SAS System                            02:40 Tuesday, November 8, 2022

1688       LIBNAME difttgt2 BASE "D:\Workshop\dift\datamart";
NOTE: Libref DIFTTGT2 refers to the same physical library as DIFTTGT.
NOTE: Libref DIFTTGT2 was successfully assigned as follows: 
      Engine:        BASE 
      Physical Name: D:\Workshop\dift\datamart
1689       
52                                                         The SAS System                            02:40 Tuesday, November 8, 2022

1690       options obs = max;
53                                                         The SAS System                            02:40 Tuesday, November 8, 2022

1691       LIBNAME difttgt3 BASE "D:\Workshop\dift\datamart";
NOTE: Libref DIFTTGT3 refers to the same physical library as DIFTTGT2.
NOTE: Libref DIFTTGT3 was successfully assigned as follows: 
      Engine:        BASE 
      Physical Name: D:\Workshop\dift\datamart
1692       
54                                                         The SAS System                            02:40 Tuesday, November 8, 2022

1693       LIBNAME difttgt4 BASE "D:\Workshop\dift\datamart";
NOTE: Libref DIFTTGT4 refers to the same physical library as DIFTTGT3.
NOTE: Libref DIFTTGT4 was successfully assigned as follows: 
      Engine:        BASE 
      Physical Name: D:\Workshop\dift\datamart
1694       
55                                                         The SAS System                            02:40 Tuesday, November 8, 2022

1695       options obs = max;
56                                                         The SAS System                            02:40 Tuesday, November 8, 2022

1696       LIBNAME difttgt5 BASE "D:\Workshop\dift\datamart";
NOTE: Libref DIFTTGT5 refers to the same physical library as DIFTTGT4.
NOTE: Libref DIFTTGT5 was successfully assigned as follows: 
      Engine:        BASE 
      Physical Name: D:\Workshop\dift\datamart
1697       
57                                                         The SAS System                            02:40 Tuesday, November 8, 2022

1698       /*==========================================================================*
1699        * Step:            One-Way Frequency                     A5HEHCWI.C00000FY *
1700        * Transform:       One-Way Frequency                                       *
1701        * Description:     Generates one-way frequency tables, computes            *
1702        *                  statistics to test for equal proportions, specified     *
1703        *                   proportions, or the binomial proportion.               *
1704        *                                                                          *
1705        * Source Table:    DIFT Parsed Target -                  A5HEHCWI.BM0000KS *
1706        *                   difttgt.parsedtarget                                   *
1707        *==========================================================================*/
1708       
1709       %let transformID = %quote(A5HEHCWI.C00000FY);
1710       %let trans_rc = 0;
1711       %let etls_stepStartTime = %sysfunc(datetime(), datetime20.);
1712       
1713       /* Access the data for DIFT Orion Target Tables Library  */
1714       LIBNAME difttgt BASE "D:\Workshop\dift\datamart";
NOTE: Libref DIFTTGT refers to the same physical library as DIFTTGT5.
NOTE: Libref DIFTTGT was successfully assigned as follows: 
      Engine:        BASE 
      Physical Name: D:\Workshop\dift\datamart
1715       %rcSet(&syslibrc);
1716       
1717       %let SYSLAST = %nrquote(difttgt.parsedtarget);
1718       
1719       %let _INPUT_count = 1;
1720       %let _INPUT = difttgt.parsedtarget;
1721       %let _INPUT_connect = null;
1722       %let _INPUT_engine = BASE;
1723       %let _INPUT_memtype = DATA;
1724       %let _INPUT_options = %nrquote(WHERE = %(state=%'OH%'%));
1725       %let _INPUT_alter = %nrquote();
1726       %let _INPUT_path = %nrquote(/Data Mart Development/Orion Reports/Custom Transformations/DIFT Parsed Target%(Table%));
1727       %let _INPUT_type = 1;
1728       %let _INPUT_label = %nrquote();
1729       
1730       %let _INPUT0 = difttgt.parsedtarget;
1731       %let _INPUT0_connect = null;
1732       %let _INPUT0_engine = BASE;
1733       %let _INPUT0_memtype = DATA;
1734       %let _INPUT0_options = %nrquote(WHERE = %(state=%'OH%'%));
1735       %let _INPUT0_alter = %nrquote();
1736       %let _INPUT0_path = %nrquote(/Data Mart Development/Orion Reports/Custom Transformations/DIFT Parsed Target%(Table%));
1737       %let _INPUT0_type = 1;
1738       %let _INPUT0_label = %nrquote();
1739       
1740       %let _OUTPUT_count = 0;
1741       
1742       %let etls_oneWayFreq = Token;
1743       %let etls_oneWayFreq_count = 1;
1744       %let etls_oneWayFreq0 = 1;
1745       %let etls_oneWayFreq1 = Token;
1746       %let byColumns = ;
1747       %let byColumns_count = 0;
1748       %let byColumns0 = 0;
1749       %let byColumns = ;
1750       %let weightColumn = ;
1751       %let weightColumn_count = 0;
58                                                         The SAS System                            02:40 Tuesday, November 8, 2022

1752       %let weightColumn0 = 0;
1753       %let etls_cellStatistics1 = %nrquote(NOCOL);
1754       %let etls_cellStatistics0 = %nrquote(2);
1755       %let etls_cellStatistics = %nrquote(NOCOL);
1756       %let etls_cellStatistics_count = %nrquote(2);
1757       %let etls_cellStatistics2 = %nrquote(NOFREQ);
1758       %let etls_tablesBinomial = %nrquote(NO);
1759       %let etls_tablesBinomialP = %nrquote(0.5);
1760       %let etls_tablesBinomialLevel = ;
1761       %let etls_exactBinomial = ;
1762       %let etls_limitCompTime = %nrquote(YES);
1763       %let etls_exactMaxtime = %nrquote(900);
1764       %let etls_exactMC = %nrquote(NO);
1765       %let procOptions = %nrquote(order=freq);
1766       %let etls_exactAdditional = ;
1767       %let tableOptions = ;
1768       %let etls_outputAdditional = ;
1769       %let etls_testAdditional = ;
1770       %let etls_formatStmt = ;
1771       %let options = ;
1772       %let NLEVELS = %nrquote(NO);
1773       %let NOPRINT = %nrquote(NO);
1774       %let etls_title1 = %nrquote(Contact Area Codes by Frequency );
1775       %let etls_title2 = ;
1776       %let etls_title3 = ;
1777       %let etls_footnote1 = ;
1778       %let etls_footnote2 = ;
1779       %let etls_ODSResult = %nrquote(ODSHTML);
1780       %let etls_htmlloc_server = %nrquote(DefaultServer);
1781       %let etls_htmlloc = %nrquote(D:\Workshop\dift\reports\ContactAreaCodeInfo.html);
1782       %let etls_htmlstyle = ;
1783       %let etls_odshtml = ;
1784       %let etls_rtfloc = ;
1785       %let etls_rtfstyle = ;
1786       %let etls_rtfAuthor = ;
1787       %let etls_odsrtf = ;
1788       %let etls_pdfloc = ;
1789       %let etls_pdfAuthor = ;
1790       %let etls_pdfkywds = ;
1791       %let etls_pdfsubjt = ;
1792       %let etls_odspdf = ;
1793       
1794       %global etls_selectedValues;
1795       
1796       %macro concatListSelection(count, keyword, separator=%quote( ));
1797       
1798          %global etls_selectedValues;
1799          %let etls_selectedValues=;
1800          %local i x;
1801          %do i=1 %to &count;
1802             %let x=&&keyword%eval(&i);
1803             %if &i=1  %then
1804                %let etls_selectedValues=&&&keyword;
1805             %else
1806                %let etls_selectedValues=&etls_selectedValues&separator&&&x;
1807          %end;
1808       %mend concatListSelection;
1809       
59                                                         The SAS System                            02:40 Tuesday, November 8, 2022

1810       %macro OneWayFreq;
1811       
1812          %let etls_no=NO;
1813       
1814       
1815          /* ODS Open Statement */
1816          %if (%quote(&etls_ODSResult) eq ODSHTML ) %then
1817          %do;
1818             %if (%quote(&etls_htmlstyle) ne) %then
1819                %let etls_htmlstyle=stylesheet=(URL="&etls_htmlstyle");
1820       
1821             %if (%quote(&etls_htmlloc) ne) %then
1822                %let etls_htmlloc=%quote(file="&etls_htmlloc");
1823       
1824             ODS html &etls_htmlstyle &etls_htmlloc &etls_odsHTML ;
1825          %end;
1826       
1827          %else %if (%quote(&etls_ODSResult) eq ODSRTF ) %then
1828          %do;
1829       
1830             %if (%quote(&etls_rtfloc) ne) %then
1831                %let etls_rtfloc=file=%quote("&etls_rtfloc");
1832       
1833             %if (%quote(&etls_rtfstyle) ne) %then
1834                %let etls_rtfstyle=stylesheet=(URL="&etls_rtfstyle");
1835       
1836             %if (%quote(&etls_rtfAuthor) ne) %then
1837                %let etls_rtfAuthor=author=%quote("&etls_rtfAuthor");
1838       
1839             ODS rtf &etls_rtfloc &etls_rtfstyle &etls_rtfAuthor &etls_odsrtf ;
1840       
1841          %end;
1842       
1843          %else %if (%quote(&etls_ODSResult) eq ODSPDF ) %then
1844          %do;
1845       
1846             %if (%quote(&etls_pdfloc) ne) %then
1847                   %let etls_pdfloc=file=%quote("&etls_pdfloc");
1848       
1849                %if (%quote(&etls_pdfkywds) ne) %then
1850                   %let etls_pdfkywds=keywords=%quote("&etls_pdfkywds");
1851       
1852                %if (%quote(&etls_pdfAuthor) ne) %then
1853                   %let etls_pdfAuthor=author=%quote("&etls_pdfAuthor");
1854       
1855                %if (%quote(&etls_pdfSubjt) ne) %then
1856                   %let etls_pdfSubjt=subject=%quote("&etls_pdfSubjt");
1857       
1858                ODS pdf &etls_pdfloc &etls_pdfAuthor &etls_pdfkywds &etls_pdfSubjt &etls_odspdf;
1859          %end;
1860       
1861          %else %do;
1862             ODS listing;
1863          %end;
1864       
1865          /* Titles */
1866          %if (%quote(&etls_title1) ne) %then
1867          %do;
60                                                         The SAS System                            02:40 Tuesday, November 8, 2022

1868             title1 &etls_title1;
1869          %end;
1870       
1871          %if (%quote(&etls_title2) ne) %then
1872          %do;
1873             title2 &etls_title2;
1874          %end;
1875       
1876          %if (%quote(&etls_title3) ne) %then
1877          %do;
1878             title3 &etls_title3;
1879          %end;
1880       
1881          /* Footnotes */
1882          %if (%quote(&etls_footnote1) ne) %then
1883          %do;
1884             footnote1 &etls_footnote1;
1885          %end;
1886       
1887          %if (%quote(&etls_footnote2) ne) %then
1888          %do;
1889             footnote2 &etls_footnote2;
1890          %end;
1891       
1892          /* Change values to blank when set to NO */
1893          %if (%quote(&etls_exactMC) eq &etls_no) %then
1894          %do;
1895             %let etls_exactMC =;
1896          %end;
1897       
1898          %if (%quote(&nlevels) eq &etls_no) %then
1899          %do;
1900             %let nlevels=;
1901          %end;
1902       
1903          %if (%quote(&noprint) eq &etls_no) %then
1904          %do;
1905             %let noprint=;
1906          %end;
1907       
1908          %if (%quote(&etls_tablesBINOMIAL) eq &etls_no) %then
1909             %let etls_tablesBINOMIAL=;
1910       
1911          %if (%quote(&etls_exactBINOMIAL) eq &etls_no) %then
1912             %let etls_exactBINOMIAL=;
1913       
1914          /* OPTIONS Statement */
1915          %if (%quote(&options) ne) %then
1916          %do;
1917             options &options;
1918          %end;
1919       
1920          /* PROC FREQ Statement */
1921          proc freq data = &_INPUT (&_INPUT_options)
1922             &nlevels &noprint &procOptions  ;
1923       
1924          /* BY Statement */
1925          %if (%quote(&byColumns) ne) %then
61                                                         The SAS System                            02:40 Tuesday, November 8, 2022

1926          %do;
1927            by &byColumns;
1928          %end;
1929       
1930          /* EXACT Statement */
1931          /* MAXTIME=value - Use only when etls_limitCompTime is YES */
1932          %if (%quote(&etls_limitCompTime) ne) %then
1933             %let etls_exactMaxtime =maxtime=&etls_exactMaxtime;
1934       
1935          /* EXACT - Additional Options */
1936          %let exact_stats =   &etls_exactBinomial &etls_exactAdditional;
1937          %let exact_compute = &etls_exactMC &etls_exactMaxtime ;
1938       
1939          %if (%quote(&exact_stats) ne) %then
1940          %do;
1941             exact &exact_stats %str(/) &exact_compute ;
1942          %end;
1943       
1944          /* OUTPUT Statement */
1945          %if (&_OUTPUT_count > 1) %then
1946          %do;
1947       		%if (%quote(&_OUTPUT1) ne ) %then
1948             	output out = &_OUTPUT1 (&_OUTPUT1_options) &etls_tablesBinomial &etls_outputAdditional;
1949          %end;
1950       
1951          /* TABLES Statement */
1952          %let etls_selectedValues=;
1953          %if (%quote(&etls_CellStatistics) ne ) %then
1954          %do;
1955             %let etls_selectedValues=;
1956             %concatListSelection(&etls_CellStatistics_count, etls_CellStatistics);
1957             %let etls_CellStatistics=&etls_selectedValues;
1958          %end;
1959       
1960       	%let _outTable = ;
1961          %if (&_OUTPUT_count > 0) %then
1962       	%do;
1963       		%if (%quote(&_OUTPUT0) ne ) %then
1964             	%let _outTable = out = &_OUTPUT0 (&_OUTPUT0_options);
1965       	%end;
1966       
1967          %if (%quote(%str(&etls_tablesBinomial)) ne ) %then
1968          %do;
1969             %if (%quote(&etls_tablesBinomialP) ne) %then
1970                %let etls_tablesBinomial =&etls_tablesBinomial(P=&etls_tablesBinomialP);
1971             %else
1972             %if (%quote(&etls_tablesBinomialLevel) ne) %then
1973                %let etls_tablesBinomial =&etls_tablesBinomial(LEVEL=&etls_tablesBinomialLevel);
1974          %end;
1975          %else
1976             %let etls_tablesBinomial=;
1977       
1978          %let tableOptions= &_outTable &etls_tablesBinomial &tableOptions;
1979          %let freqDistTables= &etls_oneWayFreq;
1980       
1981          /* Do not generate TABLES statement if no specific requests are made */
1982          %if (%quote(&etls_oneWayFreq) eq) %then
1983             %let etls_oneWayFreq=_ALL_;
62                                                         The SAS System                            02:40 Tuesday, November 8, 2022

1984       
1985          tables &etls_oneWayFreq / &tableOptions &etls_CellStatistics;
1986       
1987          /* TEST Statement */
1988          %if (%quote(&etls_testAdditional) ne) %then
1989          %do;
1990             test &etls_testAdditional;
1991          %end;
1992       
1993         /* FORMAT Statement */
1994          %if (%quote(&etls_FormatStmt) ne) %then
1995          %do;
1996             format &etls_FormatStmt;
1997          %end;
1998       
1999          /* WEIGHT Statement */
2000          %if (%quote(&weightColumn) ne) %then
2001          %do;
2002             weight &weightColumn;
2003             run;
2004          %end;
2005          %else
2006          %do;
2007             run;
2008          %end;
2009       
2010       
2011          /* Titles */
2012          %if (%quote(&etls_title1) ne) %then
2013          %do;
2014             title1 ;
2015          %end;
2016       
2017          %if (%quote(&etls_title2) ne) %then
2018          %do;
2019             title2 ;
2020          %end;
2021       
2022          %if (%quote(&etls_title3) ne) %then
2023          %do;
2024             title3 ;
2025          %end;
2026       
2027          /* Footnotes */
2028          %if (%quote(&etls_footnote1) ne) %then
2029          %do;
2030             footnote1 ;
2031          %end;
2032       
2033          %if (%quote(&etls_footnote2) ne) %then
2034          %do;
2035             footnote2 ;
2036          %end;
2037       
2038          /* ODS Closing Statement */
2039          %if (%quote(&etls_ODSResult) ne ) %then
2040          %do;
2041          	ods _all_ close;
63                                                         The SAS System                            02:40 Tuesday, November 8, 2022

2042          	ods listing;
2043          %end;
2044       
2045       %mend OneWayFreq;
2046       
2047       %OneWayFreq
MPRINT(ONEWAYFREQ):   ODS html file="D:\Workshop\dift\reports\ContactAreaCodeInfo.html" ;
NOTE: Writing HTML Body file: D:\Workshop\dift\reports\ContactAreaCodeInfo.html
MPRINT(ONEWAYFREQ):   title1 Contact Area Codes by Frequency ;
ERROR: Syntax error while parsing WHERE clause.
NOTE: Line generated by the macro variable "_INPUT_OPTIONS".
2047       WHERE = (state='OH')
                          _
                          22
                           _
                           76
MPRINT(ONEWAYFREQ):   proc freq data = difttgt.parsedtarget (WHERE = (state='OH')) order=freq ;
MPRINT(ONEWAYFREQ):  ;
MPRINT(ONEWAYFREQ):   tables Token / NOCOL NOFREQ;
MPRINT(ONEWAYFREQ):   run;

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, NOT, PUT, ^, ~.  

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

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE FREQ used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds
      
MPRINT(ONEWAYFREQ):   title1 ;
MPRINT(ONEWAYFREQ):   ods _all_ close;
MPRINT(ONEWAYFREQ):   ods listing;
2048       
2049       
2050       
2051       %rcSet(&syserr);
2052       %rcSet(&sysrc);
2053       %rcSet(&sqlrc);
2054       
2055       
2056       
2057       /**  Step end One-Way Frequency **/
2058       
2059       
64                                                         The SAS System                            02:40 Tuesday, November 8, 2022

2060       %let etls_endTime = %sysfunc(datetime(),datetime.);
2061       
2062       

[ErikLund_Jensen · Accepted solution]

Hi @Anne_A 

It works as expected with numeric values in the where-clause, but it can't handle quotes (either single ou double) in the expression. The transformation builds a macro variable _INPUT_options, where some of the parentheses and the quotes are macro escaped:

%let _INPUT_options = %nrquote(WHERE = %(State = %'OH%'%));

It throws an error when the macro variable is used, i.e. when the macro variable _INPUT_options is resolved in the Proc-statement. I think this is a bug, because the transformation should never generate macro variables that cannot resolve. 

So preprocessing with an extract (view) before the frequency transformation seems to be the only workaround. I hope this post is seen by a SAS employee in a position to act upon it.

[LinusH]

You probably right @ErikLund_Jensen but I'm a bit confused since the macro variable seem to resolve by looking in the log and the generated code?

Regardless, @Anne_A  you should open  a track to SAS tech support to have this managed/documented.

[ErikLund_Jensen]

Hi @LinusH 

I wrote a small program with relevant snippets from the transformation code to test the behaviour. 

And you are right, a %put works, so the macro variable can resolve.

The error occurs in the proc freq statement when the macro variable is resolved as a data set option.

I can't figure out what happens, because it works if the same value is copied from the log (the %put  &_INPUT_options) and used in the proc statement.

Code and log: 

data test;
  id = 1; state = 'OH';
run;

%let _INPUT = work.test;
%let _INPUT_options = %nrquote(WHERE = %(State = %'OH%'%));
%let procOptions = ;
%let etls_freqOrder = ;

%put &=_INPUT_options;

* As generated in the transformation - Fails;
proc freq data = &_INPUT (&_INPUT_options) 
		  &procOptions  &etls_freqOrder;
   tables ID/ NOCOL NOFREQ ;
run;

* Test without extra options - Fails too;
proc freq data = &_INPUT (&_INPUT_options); 
   tables ID/ NOCOL NOFREQ ;
run;

* With the input option copied from the log (%put) - Works;
proc freq data = &_INPUT (WHERE = (State = 'OH'));
   tables ID/ NOCOL NOFREQ ;
run;

136  data test;
137    id = 1; state = 'OH';
138  run;

NOTE: The data set WORK.TEST has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


139
140  %let _INPUT = work.test;
141  %let _INPUT_options = %nrquote(WHERE = %(State = %'OH%'%));
142  %let procOptions = ;
143  %let etls_freqOrder = ;
144
145  %put &=_INPUT_options;
_INPUT_OPTIONS=WHERE = (State = 'OH')
146
147  * As generated in the transformation - Fails;
148  proc freq data = &_INPUT (&_INPUT_options)
NOTE: Line generated by the macro variable "_INPUT_OPTIONS".
1    WHERE = (State = 'OH')
                      -
                      22
                       -
                       76
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, NOT,
              PUT, ^, ~.
ERROR 76-322: Syntax error, statement will be ignored.
149        &procOptions  &etls_freqOrder;
150     tables ID/ NOCOL NOFREQ ;
151  run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE FREQ used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

152
153  * Test without extra options - Fails too;


154  proc freq data = &_INPUT (&_INPUT_options);
NOTE: Line generated by the macro variable "_INPUT_OPTIONS".
1    WHERE = (State = 'OH')
                      -
                      22
                       -
                       76
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, NOT,
              PUT, ^, ~.
ERROR 76-322: Syntax error, statement will be ignored.
155     tables ID/ NOCOL NOFREQ ;
156  run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE FREQ used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

157
158  * With the input option copied from the log (%put) - Works;


159  proc freq data = &_INPUT (WHERE = (State = 'OH'));
160     tables ID/ NOCOL NOFREQ ;
161  run;

NOTE: There were 1 observations read from the data set WORK.TEST.
      WHERE State='OH';
NOTE: PROCEDURE FREQ used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds

[Anne_A]

Hi @LinusH,

Done 🙂. Let's see what happens ... !

Cheers.

[Anne_A]

Hello @ErikLund_Jensen & @LinusH,

I got an answer from Technical Support: the problem has been notified to R&D and it should be solved in one of the next versions of Viya (only). Meanwhile it is suggested to use a workaround as described below (I successfully tried it). Thanks again for your help!

Cheers

Anne.

I see that the workaround with using "%unquote" works fine, therefor I've another workaround to suggest as the "One-Way Frequency" is only a parameterized macro based transformation that is compatible with manually created ones using the "Transformation Generator". You can copy and paste the transformation in a new folder, rename it, adjust the code with adding the "%unquote" like I did in the attached example. The disadvantage is that an additional transformation is created, the advantage it's not overwritten by applying updates in the case you apply that change to the original transformation (which is technically possible). And as the customer is using SAS Data Integration Studio 4.902 (SAS 9.4 M4) there will be no other option as doing that the suggested way.