topic Re: How do you compress a macro variable created from within PROC SQL in SAS Procedures

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

DataMatt — Sat, 12 Jul 2014 02:48:37 GMT

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;

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 ]

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

Haikuo — Sat, 12 Jul 2014 03:09:57 GMT

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|

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

9 %put a1=|&a1|;

a1=|16|

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

12 %put a1=|&a1|;

a1=|16|

14 * SAS 9.3 or higher ;

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

16 %put a1=|&a1|;

a1=|16|

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

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

nirugh4846 — Sat, 12 Jul 2014 03:53:32 GMT

DATA d20140711; INPUT VAR1; DATALINES;

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]

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

DataMatt — Sat, 12 Jul 2014 06:57:11 GMT

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.

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

Ksharp — Sat, 12 Jul 2014 12:17:09 GMT

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