Desktop productivity for business analysts and programmers

How to append blanks to the front of a field in a query

Reply
N/A
Posts: 0

How to append blanks to the front of a field in a query

I want to redefine, in a query, a field to contain several blanks in front of the field itself, as follows:

' ' | table.fieldname as newfield format=$12.

Is this possible or should I first use a %let statement to set up the blanks:

%let threeblanks = ' ";

and then do something like: "&threeblanks | table.fieldname format=$12.

I've been having no luck in a stored process I'm trying to write, and would love any ideas or suggestions. Thanks in advance!!
SAS Super FREQ
Posts: 8,813

Re: How to append blanks to the front of a field in a query

Hi,
My question is WHY you want to append blanks to the front of the field?? A lot of SAS procedures will end up NOT showing leading or trailing blanks. Would you be willing to elaborate a bit more?
cynthia
N/A
Posts: 0

Re: How to append blanks to the front of a field in a query

Hi, Cynthia,

The reason for the blanks is primarily because I am converting a program from the FOCUS language to an EG Stored Process, and there are a few places in the FOCUS program that use this field with the 3 leading blanks. I know, it sounds crazy, but I just want to bring along as much of the FOCUS code logic as possible. If I can eliminate "stuff" like this blank padding, I'll be the first one to cheer.

In the meantime, do you possible have a suggestion as to how to "blank pad" a parameter of a field? Thanks!!
SAS Super FREQ
Posts: 8,813

Re: How to append blanks to the front of a field in a query

Sigh (for the FOCUS to SAS conversion)!

Here are a few ideas:
[pre]
%let blanks = %str( );

proc sql;
create table newone as
select name ,
age,
height,
right(put(name,$11.)) as newfield1 format=$11.,
put(name,$11.) as newfield2 format=$11.,
"&blanks"||name as newfield3 format=$11.,
' '||name as newfield4 format=$11.
from sashelp.class;
quit;

proc print data=newone label split='*';
label newfield1 = 'newfield1*1...5....0.'
newfield2 = 'newfield2*1...5....0.'
newfield3 = 'newfield3*1...5....0.'
newfield4 = 'newfield4*1...5....0.';
run;
[/pre]
NEWFIELD1 uses a PUT statement to ensure that the variable has a length of 11 AND that the values are right-justified in the field
NEWFIELD2 uses a simple PUT statement and you can see that the length is 11, but the extra spaces are all put at the end of the field
NEWFIELD3 uses a macro variable and a concatenate to prepend 3 spaces to the value of the field (and the new length is 11)
NEWFIELD4 uses a simple concatenate to prepend 3 spaces to the value of the field (and the new length is 11)

Depending on your desired results, any of the ways that are shown in the sample code should work for you. The original length of the NAME field in SASHELP.CLASS is 8 characters or positions. So when you look at the output, remember that "short" names like Sue or Alfred will also have trailing spaces at the end (with methods 2, 3 or 4) -- the number of trailing spaces will be different.

In your code, seem to have just one quote around your macro variable &threeblanks ("&threeblanks) -- which would be wrong. I think the simple:
[pre]
' '||name as newfield4 format=$11.
[/pre]
will work for your case without needing macro variables. I used a %STR macro function to delimit my 3 spaces, but I think the simple example will work without needing a macro variable.

Only one thing bothers me in your post and that is the statement that places in the FOCUS program use the field with 3 leading blanks. I hope you meant use the field VALUE with 3 leading blanks and NOT the field NAME with 3 leading blanks. A SAS variable or column or field name does not normally contain spaces. So, for example in SASHELP.CLASS, the field or column AGE contains the VALUE 14 for the first observation, where NAME=Alfred. So my examples all show how to put the 3 spaces in front of the field VALUE.

The only other thing I'm wondering is HOW you're going to use your newfield column. Are there comparisons where you need to do this:[pre]
where newfield = ' Alfred'; versus
where newfield='Alfred';
[/pre]
Here's an example of doing comparisons to consider:
[pre]

data testit;
length name $11;
name = 'Alfred ';
output;
name = 'Percival ';
output;
name = ' Alfred ';
output;
name = ' Alfred';
output;
name = 'Alfreda ';
output;
run;

proc print data=testit;
title 'looking at everybody';
run;

proc print data=testit;
title 'using colon modifier to limit comparison';
where name=: ' Alfred';
run;

proc print data=testit;
title 'using string';
where name = 'Alfred';
run;

proc print data=testit;
title 'using LEFT(NAME)';
where left(name) = 'Alfred';
run;

proc print data=testit;
title 'using CONTAINS';
where name contains 'Alfred';
run;
[/pre]

Hope this helps,
cynthia
N/A
Posts: 0

Re: How to append blanks to the front of a field in a query

Hi, Cynthia,

And thank you for your reply! In your reply, you said,

"In your code, seem to have just one quote around your macro variable &threeblanks ("&threeblanks) -- which would be wrong. I think the simple:

' '||name as newfield4 format=$11.

will work for your case without needing macro variables. I used a %STR macro function to delimit my 3 spaces, but I think the simple example will work without needing a macro variable."

My code sample having just the one quote was a typo on my part. Secondly, I had tried the ' '||name as newfield4 format=$11. but that did not work....I got errors. Possibly another typo.

I will take your suggestions and apply them. Sadly, I'm not far enough into this conversion effort to know at this point if I really have to worry about these spaces before a data value or not.

Incidentally, you're right.... I am wanting to prepend the spaces to the VALUE of my FIELD, and not to the actual FIELD NAME itself.

Again, I thank you for responding so quickly and so thoroughly.
Ask a Question
Discussion stats
  • 4 replies
  • 122 views
  • 0 likes
  • 2 in conversation