BookmarkSubscribeRSS Feed
omega1983
Calcite | Level 5

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

6 REPLIES 6
Reeza
Super User

translate function?

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

Jagadishkatam
Amethyst | Level 16

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
Patrick
Opal | Level 21

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
Rhodochrosite | Level 12

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
Super User Tom
Super User

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
Calcite | Level 5 Mit
Calcite | Level 5

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1051 views
  • 1 like
  • 7 in conversation