topic Re: Variable names with spaces in SAS Programming

Variable names with spaces

omega1983 — Fri, 25 Oct 2013 00:18:55 GMT

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

Re: Variable names with spaces

Reeza — Fri, 25 Oct 2013 00:23:32 GMT

translate function?

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

Re: Variable names with spaces

Jagadishkatam — Fri, 25 Oct 2013 04:15:59 GMT

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

Re: Variable names with spaces

Patrick — Fri, 25 Oct 2013 05:53:56 GMT

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

Re: Variable names with spaces

Peter_C — Fri, 25 Oct 2013 07:04:56 GMT

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?

Re: Variable names with spaces

Tom — Sat, 26 Oct 2013 00:30:29 GMT

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;

Re: Variable names with spaces

Mit — Tue, 29 Oct 2013 04:56:34 GMT

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;