BookmarkSubscribeRSS Feed
dmcwool
Fluorite | Level 6

Why is it 

 

 

data class2;
   length name $4.;
   set sashelp.class;
run;

generates 

WARNING: Multiple lengths were specified for the variable name by input data set(s). This can cause truncation of data.
 
but 
proc sql;
   create table class3
    as select name length=4,
    		  sex,
    		  age,
    		  height,
    		  weight
    from sashelp.class;
quit;
does not?
 
Both methods truncate the variable name, but a warning is only generated in the data step.
 
 
 

 

7 REPLIES 7
Ksharp
Super User
data class2;
   length name $ 4;
   set sashelp.class(rename=(name=_name));
   name=_name;
   drop _name;
run;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Two different languages.  SAS is SAS with its own feedback mechanisms.  SQL is a separate language.  

In the datastep, when you start a datastep the PDV is created with the lengths and variables you state.  Then it reads the first row of data from the set statement, and discovers that the lengths are different.  You then get a warning.

SQL does not work this way, it is creating a dataset based on the select statement directly, so no creation, then read.

dmcwool
Fluorite | Level 6

It's inconsistent though isn't it? Surely both should generate warnings, or neither should generate warnings. proc SQL and data steps are both under the SAS umbrella.

 

The reason I ask is during a migration from v8 to v9, a number of new mistmatched length warnings are generated. Some can be resolved by turning varlenchk off, but others (modify and merges) need to be resolved in code itself (either by the, imo, ugly and confusing drop/rename combo as detailed by.ksharp) or doing a simpler, if less efficient, proc sql statement to fix the mismatched lengths.

 

My concern is in that given this warning message appeared for the data step in version 9, is it likely they a similar warning message for proc sql will be generated in subsequent SAS releases?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Its not inconsistent.  SAS and SQL are two separate entities.  SAS merely implements a procedure to wrap around ANSI SQL - which do not have these warnings and such like.  Due to the differences in the way the two software works, there is differences in use.

 

With respect to this:

"The reason I ask is during a migration from v8 to v9, a number of new mistmatched length warnings are generated. Some can be resolved by turning varlenchk off, but others (modify and merges) need to be resolved in code itself (either by the, imo, ugly and confusing drop/rename combo as detailed by.ksharp) or doing a simpler, if less efficient, proc sql statement to fix the mismatched lengths."

These things should not have been in the code in first place.  A well documented, modeled approach would have resolved these issues at development time.  Things like turning system options off is not a good solution.

 

"My concern is in that given this warning message appeared for the data step in version 9, is it likely they a similar warning message for proc sql will be generated in subsequent SAS releases?" - I do not work for SAS, I cannot tell you.  

If it was me, I would be looking at the process as a whole, look through the detailed documentation and data modelling you will have, as part of the migration and include a block of changes/updates to incorporate the latest software updates into the code/model.

I suspect if the code was written under V8, then it could do with a more or less total rebuild as that is very old.  Lifecycle management at the end of the day.

dmcwool
Fluorite | Level 6

"Its not inconsistent.  SAS and SQL are two separate entities.  SAS merely implements a procedure to wrap around ANSI SQL - which do not have these warnings and such like.  Due to the differences in the way the two software works, there is differences in use."

 

I understand. However SAS could also implement a procedure that flags these length mismatches.

 

"These things should not have been in the code in first place.  A well documented, modeled approach would have resolved these issues at development time.  Things like turning system options off is not a good solution."

 

I agree, they shouldn't be there in the first place, but they are and they now need fixing. To defend the long since gone developers, these problems didn't generate warnings when they were first created, plus they might have actually been created by updates to other systems.

I don't see the difference between turning a system option off and forcing a length change via SQL personally, both are ultimately hiding a problem.

 

"I do not work for SAS, I cannot tell you.  

If it was me, I would be looking at the process as a whole, look through the detailed documentation and data modelling you will have, as part of the migration and include a block of changes/updates to incorporate the latest software updates into the code/model.

I suspect if the code was written under V8, then it could do with a more or less total rebuild as that is very old. Lifecycle management at the end of the day."

 

The process is vast (hundreds of thousands of lines of code) and legacy - >25 years old, however it's worked well all that time - no application is perfect after all but it is a trusted application. The decision has been to upgrade (due to server issues primarily) and SAS thankfully is backward compatible so very few issues have arisen.

 

A total rebuild would be expensive, time consuming and would probably result in an application with less functionality than the existing one. A difficult thing to sell! Ripping it up and starting again isn't usually a sensible approach. 

Thanks for your help, the explanations have helped clarify things.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I wont go about it, last post I promise.  No-one is blaming previous devs.  Any software development has to have lifecycle management built in.  As new versions come up, new technology is implemented, there needs to be constant moitioring, refactoring, or even re-implementation.  So what was "The process is vast (hundreds of thousands of lines of code) and legacy - >25 years old" - may now, in the this year be reduced only a few thousand lines of code, which operates quicker, more robustly, and provide more functionality rather than less.  The only reason I point this out is because its the first thing I notice when joining a company is the vast mass of undocumented, mass of code from 25 years ago, which creaking and breaking, with patches etc. And users just hate using it.  Its all about cost now against benefit in the future, which admittedly is a hard sell.

 

With regards to the wrapper procedure, you can pop an idea into the ideas section.  Me, I personally think that would be an overhead (cost vs benefit again).

Astounding
PROC Star

The warnings were originally implemented to handle cases of joining data sets in one way or another (not necessarily setting the length of a field explicitly).  And in those cases, SQL behaves differently than a DATA step.

 

select a.*, b.*;

 

If a field from both tables has different lengths, SQL figures out that it needs to use the longer length.

 

merge a b;

 

If both data sets contain a common variable, the length from A is used, even if that causes truncation of the values from data set B.  With no warning, this created problems that were very difficult to diagnose.

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!

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
  • 7 replies
  • 884 views
  • 1 like
  • 4 in conversation