Variable names with spaces

[omega1983]

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

[Reeza]

translate function?

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

[Jagadishkatam]

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

[Patrick]

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';

[Peter_C]

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?

[Tom]

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;

[Mit]

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=_:) sum=;

run;