topic Trimming a Character variable in SAS Programming

Trimming a Character variable

GregG — Thu, 14 Nov 2013 20:37:57 GMT

I have a character variable that is formatted with a length of 36.

This variable contains department names of varying lengths, from 9 all the way to, well, 36.

I create different data sets from a subset of a larger data set using variable (each department goes into its own data set).

The variable stays character (obviously) with a length of 36, but I need to remove the trailing blanks from all of those that are less than 36 length.

I've tried trim / strip / compress - none of these change the format length of the variable.

I can use put, except then I would have to input all of the different lengths.

What I would like to do (if possible) is assign the length to a macro variable and then somehow use that in the put statement, but it just isn't working (possible for a good reason).

Here is what I have tried along this line:

%let test = %length(dept); /* Dept is the variable name, and I want to count the length of it not including trailing spaces (spaced between words is okay) */

In the case of Dept = Visual Arts, I want &test to resolve to 11, instead it resolves to 4 - the length of dept.

Any help would be greatly appreciated.

Re: Trimming a Character variable

Reeza — Thu, 14 Nov 2013 20:46:54 GMT

That's because its look at the word "DEPT" not the variable dept.

I'm not quite understanding though. One variable can have only one format for all the observations, and one length for the variable, which is the maximum length. The LENGTH function will take the length of the specific observation.

VLENGTH function returns the length of the variable

LENGTHC - returns length of variable with trailing blanks

There's some other length functions as well.

So basically, what are you trying to do and why

Re: Trimming a Character variable

ballardw — Thu, 14 Nov 2013 20:53:00 GMT

You may be misunderstanding what the FORMAT means. It basically says, with a $36. format, to display the variable upto 36 characters. Format applies to the description of the Variable, not the individual values. If you have run strip on the variable there are no longer any trailing blanks, if there ever were any.

See what happens when you do something like this to see the behavior of Format:

proc print data=yourdatasetname;

var dept;

format dept $4.;

run;

Also the reason %let test = %length(dept); Resolves to 4 is that macro functions look at the text provided, in this cas dept, NOT the variable. Use the length in datastep to confirm that

Data want;

set have;

test= length(dept); /* dept is a varaible in have. Length will not return the length */

run;

proc print; var dept test;run;

also try this;

Data _null_;

x= ' a string with trailling blanks ';

y=length(x);

put y=;

run; /*look in the log for output to see how the length function deals with trailing blanks*/

Re: Trimming a Character variable

SteveNZ — Thu, 14 Nov 2013 20:59:36 GMT

This works but not too elegant:

data visual_arts ;

set have (where = (dept = 'Visual Arts')) ;

call symput('dlen',length(dept)) ;

run ;

data visual_arts (drop = dept) ;

length department $&dlen ;

set visual_arts ;

department = dept ;

run ;

Re: Trimming a Character variable

data_null__ — Thu, 14 Nov 2013 21:11:36 GMT

How about something like this based on your idea

data have;
   input dept &$32.; 
   cards; 
Visual Arts
Math
History
;;;;
   run; 

%let dept = Visual Arts;
data visual_arts;
   length dept $%length(&dept);
   set have(where=(dept eq %sysfunc(quote(&dept))));
   run; 
proc print; 
   run; 

Message was edited by: data _null_

Re: Trimming a Character variable

GregG — Thu, 14 Nov 2013 21:12:16 GMT

Well, after much playing, I got something similar to what SteveNZ suggested, but even more clunky.

Here is the the gist of the program: An academic program review for all departments at my university.

It is the same report, for all of the different departments, so we are using macros to program the logic once, and then run for the various departments (based on Major Codes, not department names - this wasn't my call).

The report would be output using a file="Academic_Program_Review_for_&Year._&Department..rtf"

That last variable would have a varying degree of spaces depending on the department name, for instance, Visual Arts would come with 24 spaces before the .rtf

My boss would not accept that, and it is their program (I'm just attempting to assist on certain aspects).

So this is what I came up with:

IF MAJOR_COHORT IN &major_list;

dept1=TRIM(put(major_cohort,$dept_INV.)); /*Because of &dept_inv., Dept1 has a length of $36 regardless of actual length of the current department name, trim does nothing here*/

CALL SYMPUT ("DEPARTMENT1",DEPT1); /*Create a variable of the department name (this was previously used in the file name above*/

%let test = %length(&department1); /*Use the above variable to count the length of the current department name, Visual Arts = 11*/

DEPT = put(DEPT1,$&test..); /*Create a new DEPT with the length of the current Department, Visual Arts would be 11*/

CALL SYMPUT ("DEPARTMENT",DEPT); /*Create a new variable for use in the .rtf file name (see above) without the trailing spaces/blanks*/

Thank you all for your time helping me.

Re: Trimming a Character variable

Reeza — Thu, 14 Nov 2013 21:20:30 GMT

Use %trim instead, either in string or create a new macro variable, see below:

data class;

set sashelp.class;

length1=length(name);

length2=lengthc(name);

length3=vlength(name);

if name='Jane' then call symput('var1', name);

run;

%let var2=%trim(&var1);

data _null_;

string1="put this is my &var1. with extra spaces";

string2="this is my %trim(&var1.) with no extra spaces";

string3="this is my &var2. with no extra spaces";

put "String1:" string1;

put "String2:" string2;

put "String3:" string3;

run;

682 data _null_;

683

684 string1="put this is my &var1. with extra spaces";

685 string2="this is my %trim(&var1.) with no extra spaces";

686 string3="this is my &var2. with no extra spaces";

687

688 put "String1:" string1;

689 put "String2:" string2;

690 put "String3:" string3;

691 run;

String1:put this is my Jane with extra spaces

String2:this is my Jane with no extra spaces

String3:this is my Jane with no extra spaces

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

Re: Trimming a Character variable

Tom — Thu, 14 Nov 2013 21:43:27 GMT

If you are generating a macro variable from a data set variable use CALL SYMPUTX to have the value automatically trimmed.

CALL SYMPUTX ("DEPARTMENT1",DEPT1);

You have confused the order of macro language execution and the compiled SAS code that the macro language has generated. So your %LET statement in the middle of a data step actually is evaluated BEFORE the data step starts.

Re: Trimming a Character variable

GregG — Thu, 14 Nov 2013 22:00:04 GMT

Tom,

I'm not doubting you at all - but perhaps I am misunderstanding you. The code that I posted above works, the %let statement in the middle works for each department length correctly.

I will definitely try call symputx to get rid of clutter.

Thank you for your time.

Now I have to find out if it is possible to append a new excel sheet to an existing excel workbook, but that is a question for another area.

Call Symputx worked perfectly - in fact, it's a bit hilarious as originally I had that line (without the x) and it was causing the spaces. I expanded to 5 lines, then 3 lines (based on Reeza's %trim suggestion) and now back to the original 2 lines, except with the x.

Re: Trimming a Character variable

Tom — Fri, 15 Nov 2013 00:03:40 GMT

To see the timing run something like this.

%let name=;

%put Before = &name;

data _null_;

set sashelp.class (obs=1);

call symputx('name',name);

%put During = &name;

run;

%put After = &name;