topic Re: How to resolve for different lengths in source and targets in SAS Data Management

How to resolve for different lengths in source and targets

kajal_30 — Mon, 03 Jun 2019 15:16:36 GMT

Hi ,

For one of column the length we are getting from the source is char 300 and but output target has char 30 and we have no control over the source and target. can we use any function like trim or something to get this work ?

source (column char 300) --> transformation(function?) --> target (column char 30)

Thanks

Kajal

Re: How to resolve for different lengths in source and targets

ballardw — Mon, 03 Jun 2019 15:34:35 GMT

@kajal_30 wrote:

Hi ,

For one of column the length we are getting from the source is char 300 and but output target has char 30 and we have no control over the source and target. can we use any function like trim or something to get this work ?

source (column char 300) --> transformation(function?) --> target (column char 30)

Thanks

Kajal

What is the 'source'? A text file, SAS data set, remote DBMS table?

What kind of "transformation" are you expecting?

If I am told the output is limited to 30 characters I need some sort of idea what should be kept and what the starting values may be plus the rules involved in getting the output.

If the desired behavior is to just accept the first 30 characters from the source then read the data that way. Or send to the target that as length 30. But kind of need to know how either step is performed to provide concrete examples.

Re: How to resolve for different lengths in source and targets

PGStats — Mon, 03 Jun 2019 22:10:23 GMT

If your import is done with a datastep :

data myNewDataset;
length mySourceVar $30;
set mySource;
run;

if done via SQL:

proc sql;
create table myNewDataset as
select
    myId,
    /* other variables */,
    mySourceVar length=30
from mySource;
quit;

With both methods, the variable (mySourceVar) will be truncated by SAS.

Re: How to resolve for different lengths in source and targets

kajal_30 — Mon, 03 Jun 2019 23:00:22 GMT

I have a source where there is a column name with length 300 when I am passing this column to the next transformation it gives me a warning saying that length of column name is less in target and there may a truncation happen

Re: How to resolve for different lengths in source and targets

LinusH — Tue, 04 Jun 2019 14:55:33 GMT

You need to add a derived mapping/calculation, like substr(left(source_column),1,30))

Re: How to resolve for different lengths in source and targets

ballardw — Tue, 04 Jun 2019 15:28:12 GMT

@kajal_30 wrote:

I have a source where there is a column name with length 300 when I am passing this column to the next transformation it gives me a warning saying that length of column name is less in target and there may a truncation happen

So show the code you are using to "pass this column to the next transformation".

AND you still have not described what you want to keep from the long string.

There is no way that a 300 character value will fit into a 30 column output. None. So you have to decide what you want and specify that as what goes into the 30 columns if you do not want random truncation notes.

Re: How to resolve for different lengths in source and targets

kajal_30 — Fri, 21 Jun 2019 03:13:55 GMT

Thanks a lot