topic Re: Extracting all variables which has length less than 32 in SAS Programming

Extracting all variables which has length less than 32

nxmogil — Thu, 11 May 2023 14:46:17 GMT

Hi Can any one please help me with the code to extract all variables only having a length less than 32 from SQL server using SAS

Re: Extracting all variables which has length less than 32

PaigeMiller — Thu, 11 May 2023 14:57:35 GMT

What do you mean by "extracting"? Do you want just a list of those variable names which have length less than 32, or do you want to create a data set(s) with those variables? Or do you want something else?

Re: Extracting all variables which has length less than 32

Tom — Thu, 11 May 2023 15:36:44 GMT

First connect to the SQL server database.

Let's assume you have done that by defining a libref named SQLSRV.

Second ask SQL Server to tell you names of the columns in the table.

So if this article https://www.mytecbits.com/microsoft/sql-server/list-of-column-names is right the code might look like this:

proc sql;
connect using SQLSRV ;
create table columns as 
select * from connection to SQLSRV 
(SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'Orders'
  ORDER BY 2
);
quit;

Once you have that you should be able to answer your question.

I would help but I don't understand what your actual question is.

If you want to know if the length of the NAME of variable is less than 32 bytes then you can use the LENGTH() function on the COLUMN_NAME variable.

If you want to know if the variable is defined to be able to store values that have a maximum length of less than 32 then you probably need to make sense of the values of the DATA_TYPE variable.

Re: Extracting all variables which has length less than 32

nxmogil — Thu, 11 May 2023 15:43:09 GMT

I want to create a dataset which has variables.having a length of 32

Re: Extracting all variables which has length less than 32

Tom — Thu, 11 May 2023 15:45:13 GMT

@nxmogil wrote:
I want to create a dataset which has variables.having a length of 32

This data step will create a dataset with three variable. All of which are defined as character with a length of 32.

data want;
  length var1 varA var97 $32;
run;

Is that what you are trying to do?

Re: Extracting all variables which has length less than 32

nxmogil — Thu, 11 May 2023 16:15:50 GMT

Actually my dataset in SQL server has few variables which has more than 32 character's length, I just want to extract only the ones which has less than 32 character length

Re: Extracting all variables which has length less than 32

Tom — Thu, 11 May 2023 16:27:32 GMT

@nxmogil wrote:
Actually my dataset in SQL server has few variables which has more than 32 character's length, I just want to extract only the ones which has less than 32 character length

Are you talking about the length of the NAME of the variable? SAS variable names are limited to 32 characters.

Are you talking about the defined maximum storage length for character variables? SAS uses only FIXED LENGTH variables but SQL Server probably has many other types of character variables. Do you know how to ask SQL Server how the variable is defined?

Are you taling about the length of the values stored in the variables? Are you asking only move the observations where the values of the selected variables has a length that is less than 32 bytes? When talking about the length of character strings you need to be clear whether the strings are using a single byte encoding, like LATIN1, or if they are using a multi-byte encoding like UTF-8. With multi-byte encodings a variable with a maximum length of 32 might not be able to store 32 characters is any of the characters require multiple bytes to be stored.

Re: Extracting all variables which has length less than 32

ChrisWoo — Thu, 11 May 2023 16:34:46 GMT

I'm not sure if it able to help you by doing this.

If you don't mind filter out all variables that have lenght <=32 and do Copy & Paste to select clause in Proc SQL.

proc sql;
describe table dictionary.columns; /*all variables in output are labels, you need to find out column name for subsequent action*/
	select *,
	length(name) as Column_Length /* 'name' is label for 'Column Name' */
	from dictionary.columns /* using dictionary to check summary of your tables/dataset */
	where libname = "SASHELP" and Memname = "AACOMP" and calculated Column_Length <=5; /* Here is the sample i used, you may refer to your dataset by changing value in Libname, Memname and Length_column */

Re: Extracting all variables which has length less than 32

A_Kh — Thu, 11 May 2023 16:55:36 GMT

Hi @Tom ,

Does a 'column length' variable exist in the DB? like: select COLUMN_NAME, DATA_TYPE, COLUMN_LENGTH
Not familiar with sql.server, just assuming if any variable exist that stores column length in it then WHERE clause could eventually pick up the required variables based on the length criteria as well.

A simple example from sas library columns:

proc sql;
	create table Columns_lt32_in_all_datasets as
		select distinct name, length, memname
			from dictionary.columns
		where libname eq 'SASHELP' and memname like 'A%' and length lt 32
	order by 2;
quit; 
proc print;run;

Re: Extracting all variables which has length less than 32

A_Kh — Thu, 11 May 2023 17:03:41 GMT

Looks like you need the variable names, that have less than 32 char..
Didn't you try the solution by @Tom, using the filter WHERE length(Column_Name) lt 32?

Re: Extracting all variables which has length less than 32

nxmogil — Thu, 11 May 2023 17:21:35 GMT

Thank you all for your responses.

I have written a query like this (nl is a library connecting to SQL Server)

proc sql;

select * from nl.contracts;

quit;

Then it thrown me an error variable ABC is not found. When Googled it, found that ABC variable is more than 32 character length. So then thought in searching a code which can pull only the variables having less than 32 character from a table in SQL Server

Re: Extracting all variables which has length less than 32

Tom — Thu, 11 May 2023 22:42:50 GMT

@nxmogil wrote:

Thank you all for your responses.

I have written a query like this (nl is a library connecting to SQL Server)

proc sql;

select * from nl.contracts;

quit;

Then it thrown me an error variable ABC is not found. When Googled it, found that ABC variable is more than 32 character length. So then thought in searching a code which can pull only the variables having less than 32 character from a table in SQL Server

A variable having a length greater than 32 would not cause any trouble.

I suspect that your actual issue is that the NAME of the variable is longer than 32.

So instead of having trouble with a variable with a name like ABC you probably had trouble with a variable with a name like A_LIKE_TO_USE_THE_VARIABLE_DESCRIPTION_AS THE_NAME_INSTEAD_OF_USING_A_NAME_AS_THE_NAME.

I don;t use SQL Server so you will have to test if the code I found on-line actually works.

But if it does you could use it to generate code. First get the list of variable names into a SAS dataset you can work with.

proc sql;
connect using NL;
create table columns as 
select * from connection to NL
(SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE upper(TABLE_NAME) = 'CONTRACTS'
  ORDER BY 2
);
quit;

Now if the number of variables is small you could just put the list of names into a macro variable. (A macro variable can hold 64K bytes). And then use the variable list with the KEEP= dataset option.

proc sql noprint;
select nliteral(column_name) 
  into :name_list separated by ' '
  from columns
  where length(column_name) <= 32
;
quit;

data want;
  set nl.contracts(keep=&name_list);
run;

If the number of variables is large then it is probably easier to use a data step to write the code to a file and then use %INCLUDE to run it.

For example you could do something like this to generate code that would let you get ALL of the variables, even the ones with names that are too long for SAS.

* generate short names and label values to use in code generation ;
data columns;
  set columns;
  length sasname $32 nliteral $60 label $255 ;
  label=column_name;
  if length(column_name) <= 32 then sasname=column_name;
  else sasname=cats('VAR_',ordinal_position);
  if label = sasname then label="' '";
  else label=quote(trim(label),"'") ;
  nliteral=nliteral(sasname);
run;

* Write the SAS part of the SELECT ;
filename code temp;
data _null_;
  file code;
  set columns ;
  if _n_=1 then put 'create table want as select ' / ' ' @;
  else put ',' @;
  put nliteral 'label=' label;
run;

* Append the SQL Server part of the select ;
data _null_;
  file code mod;
  set columns end=eof;
  if _n_=1 then put 'from connection to NL'
         / '(salect ' @;
  else put '      , ' @;
  put '[' column_name ']' @ ;
  if sasname ne column_name then put 'as ' sasname ;
  else put ;
  if eof then put ' from [CONTRACTS]' / ');' ;
run;

* Run the generated code inside PROC SQL step;
proc sql;
  connect using NL ;
%include code / source2;
quit;

So that you get something like:

proc sql;
create table want as select
  'short name'n  label=' '
, VAR_2 label='This description is too long to be used as a variable name'
from connection to NL
(select [short name]
      , [This description is too long to be used as a variable name] as VAR_2
  from CONTACTS
);
quit;

Re: Extracting all variables which has length less than 32

PaigeMiller — Thu, 11 May 2023 18:46:46 GMT

@nxmogil wrote:
I want to create a dataset which has variables.having a length of 32

A data set with a LIST of variable names that have a length of less than 32? Or a data set with the variables themselves and the values? Please clarify.

Now you want a length of 32, but originally you said less than 32. Please clarify.

Re: Extracting all variables which has length less than 32

nxmogil — Thu, 11 May 2023 20:13:36 GMT

Thank you somuch