topic Re: Recode values of a character variable to make it sas friendly for proc transpose in New SAS User

Recode values of a character variable to make it sas friendly for proc transpose

Elle — Thu, 28 Feb 2019 23:28:54 GMT

I have a dataset with a character variable taking around 300 unique values. Its length is 100 and some of the values are really long and have symbols inside too.

I need to use this variable as an id in a proc transpose, but since the values are not following sas column naming conventions I was thinking I could recode them in bulk and keep track of what code I gave to what value.

Only problem is I have no idea how to approach this.

The below code simulates my issue (test dataset - it is exaggerated though, my dataset has intelligible values followed by some codes, but you get the picture)

data test1;
	length byvar $2.;
	do i=1 to 3;
		do j=1 to 2;
			substr(byvar,j)=byte(int(65+26*ranuni(0)));
		end;
		output;
	end;
	keep byvar;
run;
data test2;
	length sensor $100.;
	do i=1 to 10;
		do j=1 to 35;
			substr(sensor,j)=upcase(byte(int(113+12*ranuni(0))));
		end;
		output;
	end;
	keep sensor;
run;
proc sql noprint;
	create table test as
	select * from test1, test2;
quit;
data test;
	set test;
	length sensor_value $5.;
	do k=1 to 3;
		dt=datetime()-rannorm(0);
		l=k+floor(rannorm(0));
		t=intnx('SECOND',dt,l,'B');
		if substr(sensor,1,1)='D' then sensor_value=byte(int(65+26*ranuni(0)))||byte(int(65+26*ranuni(0)))||byte(int(65+26*ranuni(0)))||byte(int(65+26*ranuni(0)));
		else sensor_value=put(int((676+261*ranuni(0))),$5.);
		output;
	end;
	format t datetime16.;
	keep byvar sensor t sensor_value;
run;

The troublesome variable is sensor.

Is there a way to replace in bulk all its unique values with say VAL1-VAL300?

Any other suggestions / approaches are welcome.

Re: Recode values of a character variable to make it sas friendly for proc transpose

ballardw — Thu, 28 Feb 2019 23:45:25 GMT

Here is an example of one approach. You should have the data set SASHELP.CLASS available.

This selects unique values of a variable, creates a recode value for each one and then merges the recoded value to the original data set into a new set.

proc sql;
   create table list as
   select distinct name
   from sashelp.class
   order by name
   ;
quit;

data temp;
   set list;
   length recode $ 10;
   recode = catt('var',_n_);
run;

proc sql;
   create table recoded as
   select a.*, b.recode
   from sashelp.class as a
        left join
        temp as b
        on a.name=b.name
   ;
quit;

Re: Recode values of a character variable to make it sas friendly for proc transpose

Astounding — Thu, 28 Feb 2019 23:47:46 GMT

It's certainly possible to add a variable. Would that do the trick? Just before the output statement:

V + 1;
fake_key = cats("val", put(v, z3.));

Re: Recode values of a character variable to make it sas friendly for proc transpose

Elle — Fri, 01 Mar 2019 09:16:13 GMT

Thank you for the code. It is working. Why I didn't think of this is beyond me.

Re: Recode values of a character variable to make it sas friendly for proc transpose

Elle — Fri, 01 Mar 2019 09:18:45 GMT

I am not sure I follow.

The code I provided will generate a data similar to what I have. In my system this data already exists (the test data). The question is how can I take the variable sensor from the test dataset to recode it in bulk?