topic Re: How to use a column that has special chars in it's name for data manipulation in SAS Programming

How to use a column that has special chars in it's name for data manipulation

aalluru — Mon, 28 Jun 2021 16:04:37 GMT

I have a dataset (have) with columns named as follows: time_period, vol_-1, vol_0, vol_1, vol_1000.

I want to create a column that calculates the sum of these values:

data want;
set have;
total_vol = sum(vol_-1, vol_0, vol_1, vol_1000);
run;

However, since vol_-1 has a special character in it, I am getting an error. How can I get around this problem?

Re: How to use a column that has special chars in it's name for data manipulation

Reeza — Mon, 28 Jun 2021 16:11:46 GMT

1. Re-import your data after specifying the following:

options validvarname=v7;
...
import and rest of code;
...


total_vol = sum(vol__1, vol_0, vol_1, vol_1000);

This will ensure that SAS imports the data with names that are easier to type - ie it will convert symbols to underscores so your variable will likely be vol__1 -> which could easily be mistaken with vol_1.

2. Use the name literal version to refer to a data set or variable name that has spaces or special characters.

total_vol = sum('vol_-1'n, vol_0, vol_1, vol_1000);

@aalluru wrote:

I have a dataset (have) with columns named as follows: time_period, vol_-1, vol_0, vol_1, vol_1000.

I want to create a column that calculates the sum of these values:
data want;
set have;
total_vol = sum(vol_-1, vol_0, vol_1, vol_1000);
run;
However, since vol_-1 has a special character in it, I am getting an error. How can I get around this problem?

Re: How to use a column that has special chars in it's name for data manipulation

aalluru — Mon, 28 Jun 2021 17:14:24 GMT

I did not import the data, I created the dataset.

data data;
	set info_raw;
	if <condition> then discount_grp = -1;
	else if <condition> then discount_grp = 0;
	else if <condition> then discount_grp = 1;
	else if <condition> then discount_grp = 1000;
.........
run;

After that I did some more manipulations and used the proc transpose procedure to create the have table and that seemed to work which is why I the columns got named that way

proc transpose data=freq2 out=freq3 (DROP=_LABEL_ _NAME_ ) prefix=vol_;
by time_period;
id disc_grp;
var frequency;
run;

Re: How to use a column that has special chars in it's name for data manipulation

Reeza — Mon, 28 Jun 2021 17:20:08 GMT

The concept is the same, did neither of the suggested options work for you?

@aalluru wrote:

I did not import the data, I created the dataset.
data data;
	set info_raw;
	if <condition> then discount_grp = -1;
	else if <condition> then discount_grp = 0;
	else if <condition> then discount_grp = 1;
	else if <condition> then discount_grp = 1000;
.........
run;
After that I did some more manipulations and used the proc transpose procedure to create the have table and that seemed to work which is why I the columns got named that way
proc transpose data=freq2 out=freq3 (DROP=_LABEL_ _NAME_ ) prefix=vol_;
by time_period;
id disc_grp;
var frequency;
run;

Re: How to use a column that has special chars in it's name for data manipulation

aalluru — Mon, 28 Jun 2021 17:24:45 GMT

yes the second options worked well for mine. Thank you so much!