how to remove special chracters from columns

[Kaushik1]

Following type of data I am having for processing...

data sample;
input index sales product_id $;
datalines;
1 10 100_10
1 20 10_100
1 30 1_50
1 15 12_500
2 12 100_10
2 25 10_100
2 25 1_50
2 10 12_500
;

After passing this data to PROC TRANSPOSE

proc transpose data=sample out=SampleT;
var sales;
id product_id;
by index;
run;

I am getting product_id as '_100_10' in SampleT i don't want first "_" sign to be there in data. 

Obs	index  _NAME_  _100_10  _10_100  _1_50  _12_500
 1	  1	  sales	    10	      20     30	     15
 2	  2	  sales	    12	      25     25	     10

What should I do...?

[PeterClemmensen]

This happens because a SAS variable name can not begin with a number..

So the question is rather: How do you want to variable names shaped?

[Kaushik1]

Why this problem is coming, when I am trying to execute in SAS University Edition and SAS 9.4(desktop edition) but not in SAS Studio 3.8?

[Kurt_Bremser · Accepted solution]

---

@Kaushik1 wrote:

Why this problem is coming, when I am trying to execute in SAS University Edition and SAS 9.4(desktop edition) but not in SAS Studio 3.8?

---

That is most probably because the Workspace Server behind SAS Studio is configured to run with

options validvarname=v7;

instead of validvarname=any. Which is a very good idea IMO, as it prevents you from having silly column names that need the "name literal" construct every time you use them.

[Jagadishkatam]

Please try

data sample;
input index sales product_id $;
product_id=compress(product_id,'_');
datalines;
1 10 100_10
1 20 10_100
1 30 1_50
1 15 12_500
2 12 100_10
2 25 10_100
2 25 1_50
2 10 12_500
;

proc transpose data=sample out=SampleT prefix=col;
var sales;
id product_id;
by index;
run;

[maggiem_sas]

I am studying PROC TRANSPOSE right now for the certification exam, so this is of interest to me!

The COMPRESS function doesn't work for me as it is shown in the solution, because it removes the underscore from the input data set before the transpose. You would have to use it on all the variables in the output data set.

I think the correct solution was this from @Kurt_Bremser  which allows variables to start with a number:

options validvarname=any;

Or you could add the PREFIX= option to PROC TRANSPOSE like this:

proc transpose data=sample out=SampleT prefix=n;
var sales;
id product_id;
by index;
run;

[Kaushik1]

Option validvarname=any;

works.

problem with second solution is, for different input data it will prefix 'n' which is not preferable.