Help using Base SAS procedures

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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
Respected Advisor
Posts: 3,124

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

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

View solution in original post


All Replies
Solution
‎07-11-2014 11:09 PM
Respected Advisor
Posts: 3,124

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

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

N/A
Posts: 1

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

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]

New Contributor
Posts: 3

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

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.

Super User
Posts: 9,687

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1744 views
  • 0 likes
  • 4 in conversation