BookmarkSubscribeRSS Feed
Loko
Barite | Level 11

Dear All,

Could you please explain the reason for receiving the Warning message (SAS 9.2):

character expression will be truncated when assigned to character column

when running the below code (which seems to execute correctly). The issue is that I have a macro that looks also for warnings and returns some messages which determines some future checks therefore the goal is not to have either warnings or errors in the code.

If the update is run in a data step the warning is not return, but I'd prefer the sql approach since the table might have  integrity constraints defined.

data a;

infile datalines missover;

input a :$7. b $7.;

datalines;

1234123

2345234

4321432

;

run;

proc sql;

update a

set b=transtrn(a,substr(a,3,5),'aaaaa')

/*set b=substr(a,1,2)||'aaaaa' */

where a is not missing;

quit;

10x,

Loko

9 REPLIES 9
stat_sas
Ammonite | Level 13

Hi,

If we don't assign length of a variable generated as a result of transtrn function the default length will be 200 bytes. As b is defined with length 7 bytes and value assigned to it through "set b=transtrn(a,substr(a,3,5),'aaaaa') " will be assumed as 200 bytes long. This will generate warning. Try this one to validate.

Regards,

Naeem

data a;
infile datalines missover;
input a :$7. b $7. c $200.;
datalines;
1234123
2345234
4321432
;
run;

proc sql;
update a
set c=transtrn(a,substr(a,3,5),'aaaaa')

/*set b=substr(a,1,2)||'aaaaa' */
where a is not missing;
quit;

Loko
Barite | Level 11

Hello Naeem,

Unfortunately I can not change the length of the variable since the table has already been created and the structure is long before defined; my only goal is to

update the value of this field.

It is not the matter only of the transtrn function . The commented code (the ones using the concatenation ) also generates the same warning.

It seems like proc sql does not perceive the variable as already defined and it returns a result with a length of 200 bytes which then squeezes within the variable?

stat_sas
Ammonite | Level 13

Hi Loko,

You don't need to change the length of variable to 200. This is just a warning that if the values are greater than 7 then it will be truncated. You will get  right values in your b variable becasue it is taking only 7 characters which is alright with the defined length of 7.

Regards,

Naeem

Ksharp
Super User

You can change the length of variable without changing the table structure .

proc sql;

alter table work.have;

modify c char(200) ;

quit;

Xia Keshan

Loko
Barite | Level 11

Unfortunately, the changing of the table structure is not a solution, but since the expression returns a 200 bytes value then I use the input

function which eliminates the Warning message.

Anyway, coming back to the initial issue, don't you think the warning doesn't have to appear in such caese?

10x,

L

Anotherdream
Quartz | Level 8

I don't get an error message when your exact code given is run, can you output your log by chance?

Tom
Super User Tom
Super User

So if the length of the target variable is known then use that to truncate the value that is being passed.

For example if the variable C is defined with length of 7 then you could

Change : c=transtrn(a,substr(a,3,5),'aaaaa')

To        : c=substrn(transtrn(a,substr(a,3,5),'aaaaa'),1,7)

Patrick
Opal | Level 21

Tom provided already a solution for the SQL syntax.

"I'd prefer the sql approach since the table might have  integrity constraints defined"

With a data step using MODIFY will allow you to change the table in place (instead of re-creating it) and though the table structure inclusive any indexes, constraints, audit trails & generation data sets will be maintained.

data a;

  infile datalines truncover dlm=' ' dsd;

  input a :$7. b :$7. c :$200.;

  datalines;

1234123 1 1

2345234 2 2

3 3

4321432 4 4

;

run;

proc sql;

  create index a

    on a (a);

quit;

data a;

  modify a;

  where not missing(a);

  b=substr(a,1,2)||'aaaaa';

  replace;

run;

proc contents data=a;

run;quit;

stat_sas
Ammonite | Level 13

data a;
infile datalines missover;
input a :$7. b $7.;
datalines;
1234123
2345234
4321432
;
run;
proc sql;
update a
set b=input(transtrn(a,substr(a,3,5),'aaaaa'),$7.)

/*set b=substr(a,1,2)||'aaaaa' */
where a is not missing;
quit;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 4513 views
  • 0 likes
  • 6 in conversation