topic Re: Where statement in proc to compare negparen format values, How? in SAS Programming

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

Macro — Tue, 22 Jul 2014 16:23:26 GMT

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?

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

Reeza — Tue, 22 Jul 2014 16:31:08 GMT

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

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

Macro — Tue, 22 Jul 2014 20:40:06 GMT

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;

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

Reeza — Tue, 22 Jul 2014 20:51:01 GMT

What are you trying to do?

I'm lost

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

Astounding — Tue, 22 Jul 2014 21:04:42 GMT

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?