Turn on suggestions

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

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- How do you compress a macro variable created from within PROC SQL

Options

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 07-11-2014 10:48 PM
(9082 views)

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 ]

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.