BookmarkSubscribeRSS Feed
aloou
Obsidian | Level 7

Hello,

i just tried to create a table, i have a case when clause that generates an error.

(case
when t1.code_chiffre_affaire = 1 then '<-10%'
when t1.code_chiffre_affaire = 2 then '-10% à -5%'
when t1.code_chiffre_affaire = 3 then '-5% à 0%'
when t1.code_chiffre_affaire = 4 then '0% à +5%'
when t1.code_chiffre_affaire = 5 then '+5% à +10%'
when t1.code_chiffre_affaire = 6 then '>+10%' end)
as tranche_var_chiff_aff

 

the error : 

ERROR: String data, right-truncated TRANCHE_VAR_CHIFF_AFF

8 REPLIES 8
PaigeMiller
Diamond | Level 26

Please show us the ENTIRE log for this PROC SQL, with nothing chopped out; we need to see every single character and every single line in the log for this PROC SQL.

 

Please copy and paste the log into the window that appears when you click on the </> icon.

2021-11-26 08_27_29-Reply to Message - SAS Support Communities — Mozilla Firefox.png

--
Paige Miller
SASKiwi
PROC Star

Try explicitly setting the column length like so:

(case
when t1.code_chiffre_affaire = 1 then '<-10%'
when t1.code_chiffre_affaire = 2 then '-10% à -5%'
when t1.code_chiffre_affaire = 3 then '-5% à 0%'
when t1.code_chiffre_affaire = 4 then '0% à +5%'
when t1.code_chiffre_affaire = 5 then '+5% à +10%'
when t1.code_chiffre_affaire = 6 then '>+10%' end)
as tranche_var_chiff_aff length = 20
Patrick
Opal | Level 21

I can't replicate this error. 

Can you please execute below in your environment. If that doesn't replicate the error then please post more of your code and SAS log.

Also: You should always finish a CASE statement with an ELSE case to ensure your conditions are exhaustive.

data have;
  do code_chiffre_affaire=1 to 7;
    output;
  end;
run;

proc sql;
  select 
    (case
    when t1.code_chiffre_affaire = 1 then '<-10%'
    when t1.code_chiffre_affaire = 2 then '-10% à -5%'
    when t1.code_chiffre_affaire = 3 then '-5% à 0%'
    when t1.code_chiffre_affaire = 4 then '0% à +5%'
    when t1.code_chiffre_affaire = 5 then '+5% à +10%'
    when t1.code_chiffre_affaire = 6 then '>+10%' end)
    as tranche_var_chiff_aff
  from have t1
  ;
quit;
aloou
Obsidian | Level 7
i tried this and it works perfectly , however in my code it does not do well.
Tom
Super User Tom
Super User

@aloou wrote:
i tried this and it works perfectly , however in my code it does not do well.

Which why you were asked to post the actual code, not a snippet.

 

Since your strings have some non 7bit ASCII characters I suspect that you might be seeing the impact of transcoding the strings changing the length (number of bytes) needed to store the characters.  

777  data test;
778    length x y $200 ;
779    x = '-10% à -5%';
780    y = kcvt(x,'wlatin1','utf-8');
781    lenx=length(x);
782    leny=length(y);
783    put lenx= leny=;
784  run;

lenx=10 leny=11

Just making sure the target variable is defined long enough to store the full length of the string being placed into it should eliminate the truncation error.

 

Are you sure the truncation is actual for this variable and not some other variable?

aloou
Obsidian | Level 7
here i posted the script and logs
SASKiwi
PROC Star

You are using SAS Viya and PROC CAS which many responders don't have access to. You might get a quicker response from SAS Tech Support.

Patrick
Opal | Level 21

Based on some Internet research the Error is most likely due to SAS not creating variable tranche_var_chiff_aff with a sufficient length. It's certainly worth to contact SAS TechSupport directly. Make sure you provide them with the Viya version you're using and your site number (proc setinit;run; will give you this information).

 

Like so many of us I also don't have access to a Viya playpen environment so can't test. 

You could try to use an explicit cast() statement to define the variable explicitly with a sufficient length. Below works with FedSQL under SAS9.4 M7.

data have;
  do code_chiffre_affaire=1 to 7;
    output;
  end;
run;

proc datasets lib=work nolist nowarn;
  delete test;
quit;

proc fedsql;
  create table work.test as
  select 
    cast(
      (case
        when t1.code_chiffre_affaire = 1 then '<-10%'
        when t1.code_chiffre_affaire = 2 then '-10% à -5%'
        when t1.code_chiffre_affaire = 3 then '-5% à 0%'
        when t1.code_chiffre_affaire = 4 then '0% à +5%'
        when t1.code_chiffre_affaire = 5 then '+5% à +10%'
        when t1.code_chiffre_affaire = 6 then '>+10%' end)
        as varchar(20))
      as tranche_var_chiff_aff
  from have t1
  ;
quit;

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

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
  • 8 replies
  • 1336 views
  • 3 likes
  • 5 in conversation