topic %let list = "X"; -> Literal Contains Unmatched Quote Part 2 in SAS Programming

%let list = "X"; -> Literal Contains Unmatched Quote Part 2

jerrylshen — Fri, 30 Aug 2019 15:04:12 GMT

A follow-up to: https://communities.sas.com/t5/SAS-Programming/let-list-quot-X-quot-gt-Literal-Contains-Unmatched-Quote/td-p/585064

The arrays (not_done_list, col_list, query_list, sas_check) are all aligned so that the same indexed value corresponds with each other.

%let not_done_list = SODIUMND | SODIUMND; /*column names (ND=NOT DONE) */
%let col_list      =  SODIUM | SODIUM ;   /*column names */

/* query_list and sas_check are just strings in array, not column names */  
  
%let query_list= 'querytext1' | 'querytext2' ;
%let sas_check = "CHEM005" | "CHEM006"; 


%macro create_table(not_done_list, col_list, query_list, sas_check);
proc sql noprint;
CREATE TABLE t1 AS 
SELECT DISTINCT %str(&query_list) as query_text, %str(&sas_check) as SAS_Check, Subject, %str(&not_done_list) as not_done_list, %str(&col_list) as col_list
FROM chem
GROUP BY Subject
HAVING ((put(&col_list, best.)^="" or put(&col_list, best.)="No") and not_done_list IS NOT NULL) OR 
((put(col_list, best.)="" or put(&col_list, best.)="Yes") and not_done_list IS NOT NULL)
;
quit;

%mend create_table;
 

%macro prep_table(notdone, col, query, sas_check );
%local item;
%do item=1 %to %sysfunc(countw(&not_done_list));
	%let notdone=%scan(&not_done_list,&item);
   	%let col=%scan(&col_list,&item);
	%let query=%scan(&query_list,&item, |);
 	%let sas=%scan(&sas_check,&item, |);

	%create_table(not_done_list=&notdone, col_list=&col, query_list=&query, sas_check=&sas);
%end;
%mend prep_table;

%prep_table(notdone=&not_done_list, col=&col_list, query=&query_list, sas_check=&sas_check );

Example Input:

╔═════════╦══════════╦════════╗
║ Subject ║ SODIUMND ║ SODIUM ║
╠═════════╬══════════╬════════╣
║     001 ║ No       ║        ║
║     001 ║ Yes      ║        ║
║     002 ║ Yes      ║    221 ║
║     002 ║ No       ║        ║
╚═════════╩══════════╩════════╝

No error log, but (not expected) Output:

╔════════════╦═══════════╦═════════╦═══════════════╦══════════╗
║ query_text ║ SAS_Check ║ Subject ║ not_done_list ║ col_list ║
╠════════════╬═══════════╬═════════╬═══════════════╬══════════╣
║ querytext1 ║   CHEM005 ║     001 ║ No            ║          ║
║ querytext1 ║   CHEM005 ║     002 ║ Yes           ║      221 ║
║ querytext1 ║   CHEM005 ║     002 ║ No            ║          ║
╚════════════╩═══════════╩═════════╩═══════════════╩══════════╝

What's wrong:
1. Only querytext1 is outputted (there is dirty data for querytext2, but no output).

2. ~~SAS_Check isn't right, it should be the corresponding SAS_Check via the same index in all the arrays.~~

3. not_done_list and col_list should be the value as a string

Expected output:

╔════════════╦═══════════╦═════════╦══════════╦════════╗
║ query_text ║ SAS_Check ║ Subject ║ SODIUMND ║ SODIUM ║
╠════════════╬═══════════╬═════════╬══════════╬════════╣
║ querytext1 ║ CHEM005   ║     001 ║ No       ║        ║
║ querytext1 ║ CHEM005   ║     002 ║ Yes      ║    221 ║
║ querytext2 ║ CHEM006   ║     002 ║ No       ║        ║
╚════════════╩═══════════╩═════════╩══════════╩════════╝

So, if there's a third, different column name in not_done_list and col_list, then it would be like

╔════════════╦═══════════╦═════════╦══════════╦════════╦═════════╦═══════╗
║ query_text ║ SAS_Check ║ Subject ║ SODIUMND ║ SODIUM ║ thirdND ║ third ║
╠════════════╬═══════════╬═════════╬══════════╬════════╬═════════╬═══════╣
║ querytext1 ║ CHEM005   ║     001 ║ No       ║        ║         ║       ║
║ querytext1 ║ CHEM005   ║     002 ║ Yes      ║    221 ║         ║       ║
║ querytext2 ║ CHEM006   ║     002 ║ No       ║        ║         ║       ║
╚════════════╩═══════════╩═════════╩══════════╩════════╩═════════╩═══════╝

I know this is a lot to ask, but I'm just stuck.

Thanks

Re: %let list = "X"; -> Literal Contains Unmatched Quote Part 2

Kurt_Bremser — Fri, 30 Aug 2019 14:39:39 GMT

Please post dataset chem in a data step with datalines.

Re: %let list = "X"; -> Literal Contains Unmatched Quote Part 2

jerrylshen — Fri, 30 Aug 2019 14:45:15 GMT

Added an input to main post, thanks

Re: %let list = "X"; -> Literal Contains Unmatched Quote Part 2

ballardw — Fri, 30 Aug 2019 15:15:50 GMT

@jerrylshen wrote:

A follow-up to: https://communities.sas.com/t5/SAS-Programming/let-list-quot-X-quot-gt-Literal-Contains-Unmatched-Quote/td-p/585064

The arrays (not_done_list, col_list, query_list, sas_check) are all aligned so that the same indexed value corresponds with each other.

%let not_done_list = SODIUMND | SODIUMND; /*column names (ND=NOT DONE) */
%let col_list      =  SODIUM | SODIUM ;   /*column names */

/* query_list and sas_check are just strings in array, not column names */  
  
%let query_list= 'querytext1' | 'querytext2' ;
%let sas_check = "CHEM005" | "CHEM006"; 


%macro create_table(not_done_list, col_list, query_list, sas_check);
proc sql noprint;
CREATE TABLE t1 AS 
SELECT DISTINCT %str(&query_list) as query_text, %str(&sas_check) as SAS_Check, Subject, %str(&not_done_list) as not_done_list, %str(&col_list) as col_list
FROM chem
GROUP BY Subject
HAVING ((put(&col_list, best.)^="" or put(&col_list, best.)="No") and not_done_list IS NOT NULL) OR 
((put(col_list, best.)="" or put(&col_list, best.)="Yes") and not_done_list IS NOT NULL)
;
quit;

%mend create_table;
 

%macro prep_table(notdone, col, query, sas_check );
%local item;
%do item=1 %to %sysfunc(countw(&not_done_list));
	%let notdone=%scan(&not_done_list,&item);
   	%let col=%scan(&col_list,&item);
	%let query=%scan(&query_list,&item, |);
 	%let sas=%scan(&sas_check,&item, |);

	%create_table(not_done_list=&notdone, col_list=&col, query_list=&query, sas_check=&sas_check );
%end;
%mend prep_table;

%prep_table(notdone=&not_done_list, col=&col_list, query=&query_list, sas_check=&sas_check );

Example Input:

╔═════════╦══════════╦════════╗
║ Subject ║ SODIUMND ║ SODIUM ║
╠═════════╬══════════╬════════╣
║     001 ║ No       ║        ║
║     001 ║ Yes      ║        ║
║     002 ║ Yes      ║    221 ║
║     002 ║ No       ║        ║
╚═════════╩══════════╩════════╝

No error log, but (not expected) Output:

╔════════════╦═══════════╦═════════╦═══════════════╦══════════╗
║ query_text ║ SAS_Check ║ Subject ║ not_done_list ║ col_list ║
╠════════════╬═══════════╬═════════╬═══════════════╬══════════╣
║ querytext1 ║         1 ║     001 ║ No            ║          ║
║ querytext1 ║         1 ║     002 ║ Yes           ║      221 ║
║ querytext1 ║         1 ║     002 ║ No            ║          ║
╚════════════╩═══════════╩═════════╩═══════════════╩══════════╝

What's wrong:
1. Only querytext1 is outputted (there is dirty data for querytext2, but no output).

2. SAS_Check isn't right, it should be the corresponding SAS_Check via the same index in all the arrays.

3. not_done_list and col_list should be the value as a string

Expected output:

╔════════════╦═══════════╦═════════╦══════════╦════════╗
║ query_text ║ SAS_Check ║ Subject ║ SODIUMND ║ SODIUM ║
╠════════════╬═══════════╬═════════╬══════════╬════════╣
║ querytext1 ║ CHEM005   ║     001 ║ No       ║        ║
║ querytext1 ║ CHEM005   ║     002 ║ Yes      ║    221 ║
║ querytext2 ║ CHEM006   ║     002 ║ No       ║        ║
╚════════════╩═══════════╩═════════╩══════════╩════════╝

So, if there's a third, different column name in not_done_list and col_list, then it would be like

╔════════════╦═══════════╦═════════╦══════════╦════════╦═════════╦═══════╗
║ query_text ║ SAS_Check ║ Subject ║ SODIUMND ║ SODIUM ║ thirdND ║ third ║
╠════════════╬═══════════╬═════════╬══════════╬════════╬═════════╬═══════╣
║ querytext1 ║ CHEM005   ║     001 ║ No       ║        ║         ║       ║
║ querytext1 ║ CHEM005   ║     002 ║ Yes      ║    221 ║         ║       ║
║ querytext2 ║ CHEM006   ║     002 ║ No       ║        ║         ║       ║
╚════════════╩═══════════╩═════════╩══════════╩════════╩═════════╩═══════╝

I know this is a lot to ask, but I'm just stuck.

Thanks

First thing the BEST format displays numeric values. So this

put(&col_list, best.)="No"

Is NEVER true.

One of the nice things about SAS though not obvious to beginner is that non-zero values are "true"

data example;
   input x;
   if x then put x= "is true";
datalines;
1
0
.
123
.45
1E35
;
run;

So instead of that very awkward construct other comparisons are appropriate. But since you still have provided no actual data in the form of a data step with expected results for that I can't tell what values you intended with your "No" or "Yes" comparisons.

Similar:

(put(&col_list, best.)^=""

way easier to test with Not missing(&col_list.) (assuming &col_list is a valid SINGLE variable. Big advantage, the Missing function will behavior correctly with numeric or character variables.

And can you show us the code that "worked" as intended without any macros or macro variables before you delved into writing these macros. Hint: With data.

Re: %let list = "X"; -> Literal Contains Unmatched Quote Part 2

Quentin — Fri, 30 Aug 2019 15:37:11 GMT

I suggest you start by getting %Create_Table to work.

Actually, start by getting the PROC SQL step inside of %Create_Table to work, when you just write the PROC SQL step with NO MACRO CODE. As shown, there are some problems in your SQL logic.

Then after you are happy with the PROC SQL step, write %Create_Table. Note that the parameter names you have now in create_table are odd. They have _list as a suffix, but you don't pass a list to them as an argument, you only pass a single item. This makes things more confusing later when you introduce lists. Also you don't need %STR() on your select distinct clause. Keep working on %Create_Table until you can write the two macro calls for CHEM005 and CHEM006, and get the results you want.

Then after that, work on %PREP_table. This macro has a weird name, it's really a driver for %CreateTable. It could be %CreateLotsOfTables. A bigger issue in your current version is that it has a parameter named notdone which should probably be notdone_list. Because this macro *does* take lists as a parameter. As it's written, the macro doesn't actually use the parameter values it is passed as arguments, it uses the global macro variables.

Macro language is hard. When you hit errors, you have to debug in two languages. It's often easier to start by getting working SAS code, then write a macro. And this case, then writes a second macro to call the first macros.