sas log doesn't show the correct value of Macro

[GN0001]

Hello team,

I run this code multiple times, the log doesn't show the result of macro correctly:

Thanks,

Blue Blue

data diag;
input diagcode$;
cards;
202
203
203
;
Run;
Proc SQL noprint;
select " ' " & diagcode & " ' ";
into : alist
separated by ' , '
from diag;
Quit;

%put &alist;

[ballardw]

---

@GN0001 wrote:

Hello team,

I run this code multiple times, the log doesn't show the result of macro correctly:

Thanks,

Blue Blue

data diag;
input diagcode$;
cards;
202
203
203
;
Run;
Proc SQL noprint;
select " ' " & diagcode & " ' ";
into : alist
separated by ' , '
from diag;
Quit;

%put &alist;

---

So, what does it show and what do you expect it to show? One of the issues is that your Proc SQL code shown has an ERROR that does not generate anything. So if "alist" is getting output with any value it was from code other than what you show.

122  Proc SQL noprint;
123  select " ' " & diagcode & " ' ";
                                    -
                                    22
                                    76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +,
              ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT,
              FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE,
              LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

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

124  into : alist
            -----
            180
ERROR 180-322: Statement is not valid or it is used out of proper order.

125  separated by ' , '
126  from diag;
127  Quit;

I would suggest using one of the SAS CAT functions to build a string.

[GN0001]

Hello team,
I have this code from SAS support white paper. I can send it to you if you want.
The original code has used this select " ' " || diagcode || " ' ". It gave errors with a red line under ||, that is why I changed it to &.
Regards,
Blue Blue

[ballardw]

---

@GN0001 wrote:
Hello team,
I have this code from SAS support white paper. I can send it to you if you want.
The original code has used this select " ' " || diagcode || " ' ". It gave errors with a red line under ||, that is why I changed it to &.
Regards,
Blue Blue

---

If  a paper used syntax and that syntax generates an error then you should run the syntax as originally written and if you get an error copy the text from the LOG with code and errors and all messages for the data or proc step and paste the copied text into a text box opened on the forum with the </> icon.

Pretty much guarantee that if you randomly attempt to use other language syntax that it will fail OR do something that you do not expect. & is either a logical operator for 'and' or part of a macro variable reference in general with SAS code.

[GN0001]

How to use cat functions?
Regards,
Blue Blue

[GN0001]

So, what does it show and what do you expect it to show? One of the issues is that your Proc SQL code shown has an ERROR that does not generate anything. So if "alist" is getting output with any value it was from code other than what you show.

The log itself shows the code. I need alist shows diagcodes separated by comma.

Respectfully,

Blue Blue

[Astounding]

Quite to the contrary, the log shows the value 100% correctly.  It's the results of a program, so it is actually incapable of showing the value incorrectly.

Perhaps the logic to create the macro variable is incorrect.  It certainly has an extra semicolon (at the end of the SELECT line).

What do you think the correct result would be?  What result did you actually get?

[Tom · Accepted solution]

Use the QUOTE() function to quote a string.  It is easier and it is also critical if the value could contain quotes.

data diag;
  input diagcode $;
cards;
202
203
203
;

proc sql noprint;
select quote(trim(diagcode))
  into : alist separated by ','
  from diag
;
quit;
%put &=sqlobs &=alist;

Results:

3431  %put &=sqlobs &=alist;
SQLOBS=3 ALIST="202","203","203"

If you really need the single quotes (are you using pass thru SQL?) then use optional second argument to QUOTE().

quote(trim(diagcode),"'")

And if you are not using pass thru SQL then you can use a space between the quoted values instead of the comma.  The IN operator in SAS is just as happy to have spaces instead of commas (or a mix of both). 

where diag in ("202" "203" "203")

And a macro variable without commas is much easier to use as an argument to a macro:

%mymacro(codelist=&alist)

[ChrisNZ]

& means and in this context, and SAS tries to compute a Boolean.

The concatenation operator is ||.