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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: