I have a data table where I have created a new field :
Proc sql;
Alter table TABLEX
add FIELDX varchar(30);
quit;
Proc sql;
update TABLEX as A
set FIELDX = (select FIELDY from TABLEY as B
where a.MATCHKEY = b.MATCHKEY);
quit;
The max length of FIELDY is 10. Yet the update query results in a warning that halts the program.
Am I doing something wrong or is there a workaround for this that I can apply in this situation?
I can run code with your use case without ERROR - "just" a truncation WARNING
data tableY;
length fieldY $150;
fieldY='1234567890ABC';
do matchkey=1 to 5;
output;
end;
stop;
run;
data tableX;
length fieldX $10;
do matchkey=1,3,5;
output;
end;
stop;
run;
Proc sql;
update TABLEX as A
set FIELDX =
(
select FIELDY
from TABLEY as B
where a.MATCHKEY = b.MATCHKEY
)
;
quit;
IF you are 100% sure that the strings stored in FieldY are never longer than 10 characters then you could avoid the warning by converting the string to a length of 10. But be aware that this logic will actually truncate your source string without any warning if it's longer than 10 characters.
Proc sql;
update TABLEX as A
set FIELDX =
(
select put(FIELDY,$10.)
from TABLEY as B
where a.MATCHKEY = b.MATCHKEY
)
;
quit;
proc print data=tableX;
run;
What's the length of FIELDY as defined in TABLEY. You can check with PROC CONTENTS:
proc contents data = TABLEY;
run;
Note SAS datasets only have fixed length character variables. The length of the values stored is immaterial. Its the actual defined variable length that's important.
Yeah its set to 150 for some reason. So there's no way to select that field as a smaller variable when updating my field? I know i can just make my field 150 and it would work, but just trying to see other solutions.
I can run code with your use case without ERROR - "just" a truncation WARNING
data tableY;
length fieldY $150;
fieldY='1234567890ABC';
do matchkey=1 to 5;
output;
end;
stop;
run;
data tableX;
length fieldX $10;
do matchkey=1,3,5;
output;
end;
stop;
run;
Proc sql;
update TABLEX as A
set FIELDX =
(
select FIELDY
from TABLEY as B
where a.MATCHKEY = b.MATCHKEY
)
;
quit;
IF you are 100% sure that the strings stored in FieldY are never longer than 10 characters then you could avoid the warning by converting the string to a length of 10. But be aware that this logic will actually truncate your source string without any warning if it's longer than 10 characters.
Proc sql;
update TABLEX as A
set FIELDX =
(
select put(FIELDY,$10.)
from TABLEY as B
where a.MATCHKEY = b.MATCHKEY
)
;
quit;
proc print data=tableX;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.