Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- How do you compress a macro variable created from ...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-11-2014 10:48 PM

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 ]

Accepted Solutions

Solution

07-11-2014
11:09 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-11-2014 11:09 PM

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;

Regards,

Haikuo

All Replies

Solution

07-11-2014
11:09 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-11-2014 11:09 PM

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;

Regards,

Haikuo

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-11-2014 11:53 PM

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]

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-12-2014 02:57 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-12-2014 08:17 AM

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