Sum values over repeated variable values

[NewUsrStat]

Hi guys, 

suppose to have the following dataset (a dummy dataset that reflects real data): 

data mytab;
    length 
           Value $15 
           VAR1 $80 
           VAR2 $50 
           VAR3 $50;
    
    infile datalines delimiter='|' missover dsd truncover;
    input  Value $ VAR1 $ VAR2 $ VAR3 $;
datalines;
   8 ( 20.5%)|Overall (*)||
   4 ( 10.3%)|ClassA|All|
   1 (  2.6%)||A1|A1
   1 (  2.6%)||A1|A1
   1 (  2.6%)||A1|A1
   1 (  2.6%)||A1|A1
   1 (  2.6%)||A1|A1
   1 (  2.6%)||A2|A2
   1 (  2.6%)|Class345|All|
   1 (  2.6%)||NF1|NF1
;
run;

proc print data=mytab noobs;
run;

As you can see A1 is repeated. Is there a way to get the following?

data mytab1;
    length 
           Value $15 
           VAR1 $80 
           VAR2 $50 
           VAR3 $50;
    
    infile datalines delimiter='|' missover dsd truncover;
    input  Value $ VAR1 $ VAR2 $ VAR3 $;
datalines;
   8 ( 20.5%)|Overall (*)||
   4 ( 10.3%)|ClassA|All|
   5 (  13%)||A1|A1
   1 (  2.6%)||A2|A2
   1 (  2.6%)|Class345|All|
   1 (  2.6%)||NF1|NF1
;
run;


proc print data=mytab1 noobs;
run;

In other words I would like to sum over Value for repeated and identical Var2 and Var3. 

I thought to add an Index to map repeated values and to sum over them but my issue is that "value" var is char variable and not numeric. 

Can anyone help me please?

Thank you in advance

[Tom]

You cannot ADD strings.  You can concatenate them.

But why is the variable character if it is something that you want to do arithmetic on?  Did something go wrong with your creation of the original dataset?  Your starting point looks like a summary of some other dataset? Can you share the original dataset? Can you share the code that made that summary dataset?

It should be simple to change the those strings into numbers using the INPUT() function.  Since you seem to have two different numbers in the strings you can use SCAN() to separate them.

  num1 = input(scan(value,1,'()'),32.);
  num2 = input(scan(value,2,'()'),percent32.);

[PaigeMiller]

Agreeing with @Tom that numbers should not characters.

Once you perform the conversion to have numeric values, PROC SUMMARY or PROC MEANS produces the dataset or output you want.

[Ksharp · Accepted solution]

As Tom said, you need to transform these characters into numbers and calculated/sum it again and concatenate them back .

data mytab;
    length 
           Value $15 
           VAR1 $80 
           VAR2 $50 
           VAR3 $50;
    
    infile datalines delimiter='|' missover dsd truncover;
    input  Value $ VAR1 $ VAR2 $ VAR3 $;
datalines;
   8 ( 20.5%)|Overall (*)||
   4 ( 10.3%)|ClassA|All|
   1 (  2.6%)||A1|A1
   1 (  2.6%)||A1|A1
   1 (  2.6%)||A1|A1
   1 (  2.6%)||A1|A1
   1 (  2.6%)||A1|A1
   1 (  2.6%)||A2|A2
   1 (  2.6%)|Class345|All|
   1 (  2.6%)||NF1|NF1
;
run;

data mytab2;
 set mytab;
 part1=input(scan(value,1,'( )'),best.);
 part2=input(scan(value,-1,'( )'),percent.);
run;
proc summary data=mytab2 ;
 by var1-var3 notsorted;
 var part1 part2;
 output out=mytab3(drop=_:) sum=;
run;
data mytab4;
 length value $ 20;
 set mytab3;
 value=catx(' ',part1,'(')||tranwrd(put(part2,percent7.1 -r),'  ',' ')||')';
 drop part1 part2;
run;

proc print data=mytab4 noobs;
run;