DATA Step, Macro, Functions and more

Variable names with spaces

Reply
Contributor
Posts: 59

Variable names with spaces

I have the following variables that I need to use in totals;

proc sql;

create table col as

select distinct var1

from finance_table;

quit;

sample col variables;

A NORTH

B EAST

C VA

PRIVATE / CENTRAL

Since they have spaces, and in one case a / I need to convert them to look something like this

A_NORTH

B_EAST

C_VA

PRIVATE_CENTRAL

I then want to use them for totals

For example;

Region                                                            Total

A_NORTH                                                       25

B_EAST                                                       122

C_VA                                                             44

PRIVATE_CENTRAL                              122

Any ideas

Super User
Posts: 17,775

Re: Variable names with spaces

translate function?

Variable values, which those seem to be, can stay with spaces.

Trusted Advisor
Posts: 1,128

Re: Variable names with spaces

data have;

    input var1 &$20.;

    new=compress(translate(strip(var1),'_','  /'),,'s');

cards;

A NORTH

B EAST

C VA

PRIVATE / CENTRAL

;

run;

proc print;

run;

Thanks,

Jagadish

Thanks,
Jag
Respected Advisor
Posts: 3,887

Re: Variable names with spaces

I believe below will convert any string into a valid SAS variable name.

data have;
  input var1 &$20.;
  new=substrn(prxchange('s/^\d|\W+/_/o',-1,strip(var1)),1,32);
  cards;
A NORTH
B EAST
C VA
PRIVATE / CENTRAL
PRIVATE - CENTRAL
1R2VATE - CENTRAL
;
run;

You can also use non-complying names by treating them as literals using syntax like:

'PRIVATE / CENTRAL'n = 'some string';

Valued Guide
Posts: 2,174

Re: Variable names with spaces

As you want to report totals (rather than create a table forlong term use) wh not just keep these complex names as variable labels and simplify the internal names?

Super User
Super User
Posts: 6,498

Re: Variable names with spaces

Your example does NOT look like variable names with spaces. It looks like you have character variable (is it VAR1 or REGION?) that has values with spaces in it.  Why not just run PROC FREQ on the variable?

proc freq data=finance_table;

  tables var1 ;

run;

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Variable names with spaces

Hi Omega1983

I assumed that your variable name is var1 (if it is region then replace the code by actual varname).

Thanks

data col1;

set col;

var1=compbl(tranwrd(var1,' ','_'));

var1=compbl(tranwrd(var1,'/','_'));

count=1;

run;

proc sort data=col1;

by var1;

run;

proc summary data=col1 missing nway;

by var1;

var count;

output out=want(drop=_Smiley Happy sum=;

run;

Ask a Question
Discussion stats
  • 6 replies
  • 292 views
  • 1 like
  • 7 in conversation