Where statement in proc to compare negparen format values, How?

[Macro]

Hi,

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;

      var X1;

      output out=B mean=X1;

      where X1 <= &Y;

    run;

Has error:

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?

[Reeza]

Remove the comma's from the macro variable. The where will check the underlying value, the format is only for display.

[Macro]

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 ','
         from dictionary.columns
         where libname="WORK" and memname="A";

  select compress(":"||"my_"||name) into: mdname separated by ','
         from DICTIONARY.COLUMNS
         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
         from A;
quit;

* &mdname (list of variables that play the role of Y above) is the one has comma and parentheses and coused problem;

[Reeza]

What are you trying to do?

I'm lost

[Astounding]

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?