SAS Exam A00-232 - specific question regarding comma separated list

[Rampsas1]

Hello,

Below is a specific question from A00-232 practice exam located here  https://learn.sas.com/course/view.php?id=9

(I might assume that there are different practice test versions for different users)

"Use SAS dictionary tables, run a PROC SQL query to create a macro variable named COL_LIST which contains a comma separated list of the columns contained in the CERT.AIR10 data set"

i) Per SAS, the correct answer is: DATE, AIR << with a comma + space delimiter

      >>> select name into :col_list separated by ', '

ii) If we are indeed using a comma separated list, then the correct answer is: DATE,AIR << with a comma delimiter ONLY

      >>> select name into :col_list separated by ','

I answered as in ii) but it was marked incorrect.

Not an issue on a practice exam but it might be an issue on the actual exam.

Any observations on my assessment?

many thanks

[Tom]

You are right.  Hopefully they QC the questions on the actual exam better.

[dxiao2017]

Hi, I encountered the same problem while doing that question, for a while I was wondering should I always add a blank after the comma like this ', '  while taking the real exam

[dxiao2017]

Or, should the correct answer be like this:

proc sql;

select strip(name)

   into :xxxlist separated by ','

   from xxxdataset;

quit;

i.e., the blank between values was removed through the strip() function

[Tom]

---

@dxiao2017 wrote:

Or, should the correct answer be like this:

proc sql;

select strip(name)

   into :xxxlist separated by ','

   from xxxdataset;

quit;

i.e., the blank between values was removed through the strip() function

---

NO.  SEPARATED BY already removes the leading/trailing spaces.  Example:

3    proc sql noprint;
4    select name into :namelist separated by '|'
5    from sashelp.class(obs=3)
6    ;
7    quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


8    %put "&namelist";
"Alfred|Alice|Barbara"

[dxiao2017]

Hi Tom,

Thanks a lot for the reply!

The example in my post was from the official training course SQL1, I do remember there was difference when you use or do not use the strip() function. I'll find the codes in SQL1 lesson, do some more research, and reply you again. 

Best

[Tom]

---

@dxiao2017 wrote:

Hi Tom,

Thanks a lot for the reply!

The example in my post was from the official training course SQL1, I do remember there was difference when you use or do not use the strip() function. I'll find the codes in SQL1 lesson, do some more research, and reply you again. 

Best

---

Just because something is in a training class does not make it correct. There are many reasons why something incorrect can appear in a training class.  Teacher's make mistakes. Printers make mistakes.  

Better to run your own tests and see what actually happens.

The STRIP() function removes the leading/trailing spaces, but the SEPARATED BY option in PROC SQL also does that so the STRIP() is not needed here.  But there are places where it would make a difference.  For example removing the LEADING spaces will make possible to match strings that only differ by the number of leading spaces.  So 

strip('     ABC  ') = 'ABC' 

Would be true.

But then again so would 

left('     ABC  ') = 'ABC' 

Which will result in SAS doing this comparison.

'ABC       ' = 'ABC'

Which is TRUE since trailing spaces are ignored when comparing strings in SAS.

[dxiao2017]

Hi Tom, Thanks a lot for your clarification, they are really helpful. In my last post I said “I do remember there was difference when you use or do not use the strip() function”. The example is from SAS SQL1: Essentials' course notes, demo s106d02. The codes and results are as follows. The difference is in the output table in the results, not in the list of macro variable values in the log.

[Tom]

So that is another example of something I would tell users NOT to do.

If you are running a SELECT statement in SQL to populate macro variable(s) then make sure to use the NOPRINT option in the PROC SQL statement to prevent PROC SQL from also writing the result to the open output destinations.

[dxiao2017]

The reasons that I omit the NOPRINT option are as follows:

1) while learning, I always want to know what is/are the default output(s) (every possible things including results and log) of a step (without any option);

2) without NOPRINT option, the values of the macro variable created by the SQL step will be displayed directly in the output destination, which means I can easily verify whether the macro values are created and resolved correctly in the SQL step and next steps (e.g., the next step could be a macro that print out rows separately according to each level of a categorical variable) by looking at the results altogether WITHOUT clicking, opening, and checking the log, also this saves labor of writing the %put &xxx statement;

3) however, I understand this SQL step (that creates a macro variable list according to the levels of a categorical variable) always serves as an intermediate step (i.e., the product of this step is to be used in next steps), and therefore, I would NOT omit the NOPRINT option if I want the tables in this output destination to be my final product.