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
translate function?
Variable values, which those seem to be, can stay 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
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';
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?
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.