Desktop productivity for business analysts and programmers

How to find out the longest of value for an char variable?

Accepted Solution Solved
Reply
Contributor
Posts: 61
Accepted Solution

How to find out the longest of value for an char variable?

Hi,

 

I have an variable called "SUPPORT_CUST_NAME". It is defined as "Char" type with leng of 100. it is labled as "SupportCustName".

 

Dataset name is "ELT.ELT_SEC_SUPPORT_BY".

 

The problem is that the value of "SUPPORT_CUST_NAME" seems chopped much less than 100. Many names are chopped. I need to find the longest value to confirm my finding?

 

How do I find out the longest length of value for "SUPPORT_CUST_NAME" with its primay key "CR_APPL_NO"?

 

Please help with SAS code!

 

Thanks!

Nancy

 

 


Accepted Solutions
Solution
‎06-28-2016 08:03 AM
Respected Advisor
Posts: 3,068

Re: How to find out the longest of value for an char variable?

[ Edited ]
proc sql;
  select max(length(SUPPORT_CUST_NAME))
  from ELTElend.ELT_SEC_SUPPORT_BY
  ;
quit;

View solution in original post


All Replies
Respected Advisor
Posts: 5,005

Re: How to find out the longest of value for an char variable?

Before you examine the lengths, the first step would be to examine the format.  If a variable is defined as $100 but has a format of $50., you will only see the first 50 characters.

Contributor
Posts: 61

Re: How to find out the longest of value for an char variable?

How to write the code to find out?

 

Here is my code to look at the table:

 

data elendSUPPORTBY;
	set ELTElend.ELT_SEC_SUPPORT_BY;


Title "Support by in ELT ELEND";
proc print data= elendSUPPORTBY;
run;

Thanks!

 

Solution
‎06-28-2016 08:03 AM
Respected Advisor
Posts: 3,068

Re: How to find out the longest of value for an char variable?

[ Edited ]
proc sql;
  select max(length(SUPPORT_CUST_NAME))
  from ELTElend.ELT_SEC_SUPPORT_BY
  ;
quit;
Contributor
Posts: 61

Re: How to find out the longest of value for an char variable?

Hi SASKiwi,

 

Thank you so much for the SAS SQL query! The SAS query works very well. 

 

Does the embedded sql query follow standard SQL syntax?  Do they follow Oracle or DB2 or Microsoft standard?

 

Thanks!

Nancy

Grand Advisor
Posts: 17,461

Re: How to find out the longest of value for an char variable?

SAS SQL doesn't necessarily follow ANSI SQL standards. Most basic queries such as the one here, can be used in both Oracle and SQL.  

 

SAS FEDSQL is ANSI standard.

 

Oracle SQL is different than Microsoft SQL.  

 

You can use SQL pass through if you want to pass a query directly to your server.

Respected Advisor
Posts: 5,005

Re: How to find out the longest of value for an char variable?

The easiest way to find out formats would be:

 

proc contents data=eltelend.elt_sec_support_by;

run;

Contributor
Posts: 61

Re: How to find out the longest of value for an char variable?

Hi Astounding,

 

Thank you for your suggestion. Yes. I have tried proc contents, that is how I get to know this variable SUPPORT_CUST_NAME is char type with length of 100. But somehow only display 40 chars.

 

It may due to an error in ETL process.

 

Thank you so mcuh for help!

 

Nancy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 367 views
  • 0 likes
  • 4 in conversation