Using substr in Proc SQL update

[cosmid]

Hi,

How do I use a substr in a Proc Sql Update?

For example, 

data test;
  input x $;
datalines;
abc
bbc
ccc
run;

data test;
   set test;

   substr(x,1,1) = 't';
run;

This will give me:
tbc
tbc
tcc

I tried using the substr in a PROC SQL Update but it won't work:

proc sql; 
  update test
  set substr(x,1,1) = 't';
quit;

1. How do I use the substr function in a Proc SQL Update.

2. Is it okay to use the same dataset in a set statement, like the dataset test in the above example.

Thanks!

[PaigeMiller]

While you can use the SUBSTR() function in PROC SQL, I don't think you can you can use the version of the SUBSTR() function on the left of the equal sign.

So, you most likely have to do this in a DATA step.

[Reeza · Accepted solution]

Agreed with Paige, likely not possible to use this instance of SUBSTR (LEFT OF) in SQL.
You could use a combination of other functions to achieve the same output in SQL or you can use a data step.

Something like this perhaps:

proc sql;
update test
set x = catt( 't', substr(x, 2));
quit;

[Reeza]

Unless you're working with a DB, SAS actually recreates the dataset when using an UPDATE statement so it's not particularly more efficient than other steps AFAIK. I think if you're working on a DB there are some functions that will be more efficient but YMMV.

[Patrick]

@cosmid  Below code is fully working using a SQL Update.

data test;
  input x $;
  row+1;
datalines;
abc
bbc
ccc
;

proc sql;
  delete from test
  where row=2
  ;
quit;

proc sql;
  update test
  set x=catt('t',substrn(x,2))
  where row=3
  ;
quit;

proc contents data=test;
run;quit;

---

@Reeza wrote:
Unless you're working with a DB, SAS actually recreates the dataset when using an UPDATE statement so it's not particularly more efficient than other steps AFAIK. I think if you're working on a DB there are some functions that will be more efficient but YMMV.

---

@Reeza SQL changes tables in place where possible which is why deleted observations remain physically in the table.

[Kurt_Bremser]

The use of SUBSTR on the left side of an assignment is a feature of the data step compiler and not available anywhere else.