BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DataMatt
Calcite | Level 5

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
Haikuo
Onyx | Level 15

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

4 REPLIES 4
Haikuo
Onyx | Level 15

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
Calcite | Level 5

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
Calcite | Level 5

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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