CODEWITHSUNDEEP
sqlsas
Michael Ancel
Michael Ancel

Reputation: 95

SAS Proc SQL string truncation issue

Have the creation of a simple table from values in another table below:

create table summary3 as
    select 
        substr(&Start_dt.,1,4) as time_range, 
        NFDPs ,
        NFDPExceeds,
        NblkExceeds,
        NFDPExceedsLT30s as NFDPExceedsLT30,
        NReports as Nbr_report ,
        prcnt_FDP_ext ,
        prcnt_blk_ext , 
        prcnt_extLT30 as prcnt_ext_LT30,
        prcnt_report,
        monotonic() as id
    from OAP_exceedances_by_year;

my problem is arising on the very first column i created, time_range. When i try adding values to this table later on, I noticed that this column is capped to char's of length 4 or shorter, and it automatically truncates anything greater. Is there a way I can either change that first statement, or perhaps my future insert / set statements to avoid the truncation? IE i still want the first row to only be 4 characters but I may need future rows to be more.

Thanks!

Upvotes: 1

Views: 1449

Answers (1)

Joe
Joe

Reputation: 63434

This depends on how you do your future processing. If your data step later on says

data summary_final;
  set summary3;
  time_range = "ABCDEF";
run;

Then you could just change it like so:

data summary_final;
  length time_Range $6;
  set summary3;
  time_range = "ABCDEF";
run;

But you certainly could do what you say also in the initial pull. For example...

proc sql;
  create table namestr as 
  select substr(name,1,4) as namestr length=8
  from sashelp.class;
quit;

That creates namestr as length=8 even though it has substr(1,4) in it; the names there will be truncated, as the substr asks it to, but future names will be allowed to be 8 long.

Upvotes: 3

Related Questions