07-22-2014 12:23 PM
The data A is in the format of NegParen16 (comma delimited, negative number in parentheses format), Y is a macro variable with value 6,243,423 get from an observation of X1.
The following procedure
proc univariate data=A noprint;
output out=B mean=X1;
where X1 <= &Y;
Syntax error while parsing where clause.
Basically it has X1 <= 6,243,423. It cannot handle these commas.
Q: How do fix it without changing the data format of data A?
07-22-2014 12:31 PM
Remove the comma's from the macro variable. The where will check the underlying value, the format is only for display.
07-22-2014 04:40 PM
Indeed, I used the follwoing to generate 100 macro variables my_xx since data A has 100 variables:
proc sql noprint;
select name into :dname separated by ','
where libname="WORK" and memname="A";
select compress(":"||"my_"||name) into: mdname separated by ','
WHERE LIBNAME="WORK" AND MEMNAME="A";
* How do you modify the following so that my_XX macro variables do not have commas and parentheses?;
select &dname into &mdname
* &mdname (list of variables that play the role of Y above) is the one has comma and parentheses and coused problem;
07-22-2014 05:04 PM
The COMPRESS function can remove more than blanks. You just have to tell it what to remove, or what to keep. You would do this by adding between ||name and the closing parenthesis. Some examples:
compress(var) removes blanks
compress(var, '( ),') removes parentheses, blanks (because there is a blank between the quoted parentheses), and commas.
compress(var,,'kd') keeps digits only and discards the rest of the characters
Your problem may be more complex than this because you may need to replace parentheses with a negative sign, rather than just removing them.
Are we getting closer? Would it make sense to go back to Reeza's original suggestion and change the earlier code that creates the macro variables instead of fixing the problem later?