topic Using substr in Proc SQL update in New SAS User

Using substr in Proc SQL update

cosmid — Tue, 05 Oct 2021 21:28:56 GMT

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!

Re: Using substr in Proc SQL update

PaigeMiller — Tue, 05 Oct 2021 22:04:33 GMT

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.

Re: Using substr in Proc SQL update

Reeza — Tue, 05 Oct 2021 21:57:14 GMT

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;

Re: Using substr in Proc SQL update

Reeza — Tue, 05 Oct 2021 21:59:09 GMT

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.

Re: Using substr in Proc SQL update

Patrick — Wed, 06 Oct 2021 00:35:45 GMT

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

Re: Using substr in Proc SQL update

Kurt_Bremser — Wed, 06 Oct 2021 07:09:54 GMT

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