topic Re: how to remove special chracters from columns in SAS Programming

how to remove special chracters from columns

Kaushik1 — Mon, 20 May 2019 09:41:59 GMT

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...?

Re: how to remove special chracters from columns

PeterClemmensen — Mon, 20 May 2019 09:49:50 GMT

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?

Re: how to remove special chracters from columns

Jagadishkatam — Mon, 20 May 2019 09:49:59 GMT

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;

Re: how to remove special chracters from columns

Kaushik1 — Mon, 20 May 2019 10:00:36 GMT

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?

Re: how to remove special chracters from columns

Kurt_Bremser — Mon, 20 May 2019 10:07:37 GMT

@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.

Re: how to remove special chracters from columns

maggiem_sas — Tue, 21 May 2019 14:40:59 GMT

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;

Re: how to remove special chracters from columns

Kaushik1 — Tue, 21 May 2019 06:17:19 GMT

Option validvarname=any;

works.

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