CODEWITHSUNDEEP
sasmacroscharacter
roadrunnr23
roadrunnr23

Reputation: 5

SAS remove spaces and last 3 characters of a macro variable

I'm a new SAS user and I'm working on making some code modular. I want to create a macro variable to reference any county name that's formatted within the data, and a second macro variable that removes spaces from the first macro variable to create a resolved macro to be used in a dataset name. Here's where I am:

options symbolgen;
%let county_formatted=CONTRA COSTA CO.;
%let county=%sysfunc(substr(%sysfunc(compress(&county_formatted)), 1, %sysfunc(length(%sysfunc(&county_formatted)))-3))));
run;
options nosymbolgen;

Within the dataset, county names are formatted to look like "CONTRA COSTA CO.". I'd like to take any county name and compress it for the purpose of creating output datasets with the macro resolving to "contracosta" or "CONTRACOSTA". All values for county_formatted have "CO." as the last three characters, and some county names have more than one word, such as "CONTRA COSTA CO.", "SAN LUIS OBISPO CO.", or "SHASTA CO.". I want to land on "CONTRACOSTA", "SANLUISOBISPO", or "SHASTA" for the resolved value of &county.

From the above code, I get the following error: ERROR: Expected close parenthesis after macro function invocation not found.

I tried building from the inside out and was able to compress CONTRA COSTA CO. as needed to CONTRACOSTACO. but can't seem to remove the last 3 characters.

I'd appreciate any help in correcting my code.

Thanks

Upvotes: 0

Views: 903

Answers (2)

Richard
Richard

Reputation: 27508

You probably don't want to use macro for the transformation you envision. You didn't go into any detail about to be used in a dataset name, so I'll presume you have a data splitting macro that relies on a data value for the output name.

PRXCHANGE can perform regular expression substitutions.

Your use case of removing spaces and a trailing text would use a regex such as

prxchange('s/ |CO. *$//', -1, company)

data have;
length company $20 seq sales 8;
input company & seq & sales;
datalines;
CONTRA COSTA CO.      1  100
CONTRA COSTA CO.      2   90
CONTRA COSTA CO.      3  110
CONTRA COSTA CO.      4  110
CONTRA COSTA CO.      5  120
CONTRA COSTA CO.      6   80
SAN LUIS OBISPO CO.   1  200
SAN LUIS OBISPO CO.   2  210
SAN LUIS OBISPO CO.   3  220
SAN LUIS OBISPO CO.   4  230
SHASTA CO.            1   50
SHASTA CO.            2  150
SHASTA CO.            3  250
;

proc sql;
  create table splitbase1 as
  select
    prxchange('s/ |CO. *$//', -1, company) as outname length=20
  , *
  from
    have
  ;

* split "have" into sub-tables whose names are specified by outname;
....

Upvotes: 0

whymath
whymath

Reputation: 1394

This will works:

%let county_formatted=CONTRA COSTA CO.;
%let county_compressed=%sysfunc(compress(&county_formatted.));
%let county=%sysfunc(substr(&county_compressed., 1, %length(&county_compressed.)-3));
%put ***&county.***;

Your question comes from %sysfunc(length(%sysfunc(&county_formatted)))-3), the length() function returns length of raw string, it is with space.
By the way, SAS has %length(), the macro version of %sysfunc(length()).

Upvotes: 0

Related Questions