How do you compress a macro variable created from within PROC SQL

[DataMatt]

I can't seem to remove trailing spaces in a macro variable created from within PROC SQL.  A LENGTH can be assigned to the variable in the SELECT statement to force the effect but only if I know the length ahead of time does that make sense to do.  The same situation for text variables.  I have been creating the data set variable in PROC SQL and then using a DATA step with CALL SYMPUT to create the macro variable.  That works but adds unnecessary lines of code.

Here is the new code I want to use:

DATA d20140711; INPUT VAR1; DATALINES;

1

20

300

4000

50000

; RUN;

PROC SQL NOPRINT;

SELECT COMPRESS(PUT(AVG(VAR1), BEST.), ' ')

INTO : VAR1_AVG

FROM d20140711;

QUIT;

%PUT [VAR1_AVG=&VAR1_AVG];

The SAS log contains this text:  [VAR1_AVG=10864.2     ]

[Haikuo · Accepted solution]

There are many ways (almost too many IMO) to eliminate the blanks, the following is the copy - paste version credited to @Tom,

2    proc sql noprint ;

3      select max(age) into :a1 from sashelp.class;

4    %put a1=|&a1|;

a1=|      16|

5    %let a1=&a1;

6    %put a1=|&a1|;

a1=|16|

7

8      select max(age) into :a1 - :a1 from sashelp.class;

9    %put a1=|&a1|;

a1=|16|

10

11     select max(age) into :a1 separated by 'anything' from sashelp.class;

12   %put a1=|&a1|;

a1=|16|

13

14   * SAS 9.3 or higher ;

15     select max(age) into :a1 -  from sashelp.class;

16   %put a1=|&a1|;

a1=|16|

17

18     select max(age) into :a1 trimmed from sashelp.class;

19   %put a1=|&a1|;

a1=|16|

20   quit;

The original link is as:

Regards,

Haikuo

[nirugh4846]

DATA d20140711; INPUT VAR1; DATALINES;

1

20

300

4000

50000

; RUN;

PROC SQL NOPRINT;

SELECT COMPRESS(PUT(AVG(VAR1),BEST7.),' ') INTO:VAR1_AVG FROM d20140711;

QUIT;

%PUT [VAR1_AVG=&VAR1_AVG];

The SAS log contains this text: [VAR1_AVG=10864.2]

[DataMatt]

When the datalines change to all single digits, for instance, then the best7. format still pads 6 spaces.  To compress the resulting macro variable I found Hai.kuo's use of separated by 'anything' to do the trick.  But thanks for the quick reply.

[Ksharp]

And you also can use %left( &VAR1_AVG )   %trim(&VAR1_AVG) to avoid these annoying blanks.