topic Re: Question on creating a macro variable for loading comma separated list of values in SAS Programming

Question on creating a macro variable for loading comma separated list of values

radhap — Fri, 07 Aug 2020 19:29:43 GMT

I took SAS Advanced practice exam couple of days back and I have some doubts on one question regarding creating a macro variable that contains list of comma-separated values.

Here is the question from the exam:

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. If needed, use the DESCRIBE TABLE statement to display the column attributes of the dictionary.columns table. Question: What is the value of the col_list macro variable?

I answered: DATE,AIR

Per exam result: my answer was wrong

Solution given by SAS:

proc sql ;

select distinct name into :col_list separated by ', '

from dictionary.columns

where libname='CERT' and memname="AIR10";

quit;

The correct answer is: AIR, DATE

Could someone please help me understand:

Why the ‘distinct’ keyword and 'comma with blank space' are used.

Thanks.

Re: Question on creating a macro variable for loading comma separated list of values

ballardw — Fri, 07 Aug 2020 19:47:27 GMT

Distinct so each result only appears once

If you look very closely at the code

 by ', '

you can tell that there is a space inside the quotes. Make the font larger or move your cursor and you can see it. The "separated by" can be any string, even something odd like ",,,,word,,,,,". Then the result would be Air,,,,word,,,,,Date

The "why" of including the space depends on the programmer and probably the likely use of the macro variable. If I am creating a string for parameters I might not include the space but if I intend a human to read the results the space with the comma has a nicer appearance and follows typical text formatting rules.

And Proc SQL will return the values in sorted order by default. So Air before Date

Re: Question on creating a macro variable for loading comma separated list of values

Astounding — Fri, 07 Aug 2020 19:49:01 GMT

Here's something you might be able to check ...

I believe that SQL returns the values in the order in which they appear in the data. So you might be right about

DATE, AIR

except for one thing. Adding the keyword DISTINCT to the SQL query will force SAS to track the values so it makes life easier to track them by alphabetizing the extracted values.

Re: Question on creating a macro variable for loading comma separated list of values

radhap — Fri, 07 Aug 2020 19:51:33 GMT

Hi Ballardw

Thanks for the quick response. the ', ' is in the answer (key) provided after the exam. But in the question itself, I do not see any hint of the space

Re: Question on creating a macro variable for loading comma separated list of values

radhap — Fri, 07 Aug 2020 19:53:00 GMT

And also, in the question, it does not mention about unique/ distinct values

Re: Question on creating a macro variable for loading comma separated list of values

ballardw — Fri, 07 Aug 2020 19:56:07 GMT

@radhap wrote:

Hi Ballardw

Thanks for the quick response. the ', ' is in the answer (key) provided after the exam. But in the question itself, I do not see any hint of the space

I would bring that up to who ever provided this information.

But since we can't see the question in any way then who knows. One of the reasons code really should never appear in proportional fonts is because of things like this. Some fonts can be very hard to tell if a space exists or not.

Re: Question on creating a macro variable for loading comma separated list of values

radhap — Fri, 07 Aug 2020 20:41:04 GMT

This is the question, I am copy pasting the question directly from the exam, as is:

What is the value of the col_list macro variable?

Re: Question on creating a macro variable for loading comma separated list of values

Tom — Fri, 07 Aug 2020 20:52:38 GMT

@radhap wrote:

Hi

This is the question, I am copy pasting the question directly from the exam, as is:

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. If needed, use the DESCRIBE TABLE statement to display the column attributes of the dictionary.columns table.

What is the value of the col_list macro variable?

So your answer is RIGHT. SQL does not impose an order on the result set unless you request one and they did not ask for one. Acceptable answers could include

DATE,AIR
AIR,DATE

(note the CASE of the letters might be different, depending on how they are in the CERT.AIR10 dataset and whether the VALIDVARNAME option is set to UPCASE).

They did not say anything about adding spaces between the values, but if spaces are allowed then you could also generate more permutations of those two answers by including space before, after or on both sides of the comma.

In that case their answer would also be correct.

Re: Question on creating a macro variable for loading comma separated list of values

Reeza — Fri, 07 Aug 2020 21:30:48 GMT

What was your query?

This topic is covered in depth in the Advanced Prep Certification Guide,Chapter 10.

I don't like this question because you can answer it correctly WITHOUT knowing how to actually create macro variables from a SQL query.

@radhap wrote:

Hi

This is the question, I am copy pasting the question directly from the exam, as is:

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. If needed, use the DESCRIBE TABLE statement to display the column attributes of the dictionary.columns table.

What is the value of the col_list macro variable?

Re: Question on creating a macro variable for loading comma separated list of values

radhap — Fri, 07 Aug 2020 21:36:40 GMT

Thank you Tom.

I lost 3 points because of not adding distinct keyword before the name variable and space after comma (since they were not asked in the question)

Now i am confused about which approach I should follow in case I get a similar question in the main exam.