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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.