Capture actual length in proc sql for numeric foramted data

[raja777pharma]

Hello Team,

I am trying to find the actual length in proc sql for numeric data, but the length value is not coming as exptected, but in data step the length is coming correctly with function VVALUE/VVALUEX , is any way to use the same in proc sql.

The formated numric value is : 72.00 , i need lenght as for this value as '5' , but the same value is not worknig in proc sql , working on data step.

Apology for capturing the data in screenshots 

My output Results with proc sql: Not coming as expected with next data step below.

Proc sql notprint;
create table length_ds as
select EGSTRESN, length(strip(put(EGSTRESN,best32.))) as var_length
from saslib.eg(where=(not missing(EGSTRESN));
quit;

Data step results as below;

data length_ds;
  set saslib.eg(where=(not missing(EGSTRESN));;
  var1 = vvalue(EGSTRESN);
  var_length =  length(strip(put(var,best.)));
  keep var1 var_length  EGSTRESN;
run;

Thakn you,

Raj.

[ballardw]

Unless your data set SASLIB.EG has a variable named VAR that data step is not returning the value you show.

data length_ds;
  set saslib.eg(where=(not missing(EGSTRESN));;
  var1 = vvalue(EGSTRESN);
  var_length =  length(strip(put(var,best.)));
  keep var1 var_length  EGSTRESN;
run;

The Length function is NOT using the VAR1, or the Vvalueof the variable.

What is the actual format assigned to the variable EGSTRESN? You are very carefully not providing a description of your data. The ASSIGNED format is used by vvalue function for the return. So use that format in the SQL code for this, not BEST32 which will remove any decimals for integer values.

An example you can run that shows the assigned format creates different results from Vvalue.

data _null_;
   x=5;
   y=5;
   format x f4.2 y best2.;
   x1= vvalue(x);
   y2= vvalue(y);
   put _all_;
run;

What is the purpose of this exercise??

[raja777pharma]

Hi ,

In my post VAR was a typo error .

data length_ds;
  set saslib.eg(where=(not missing(EGSTRESN));;
  var1 = vvalue(EGSTRESN);
  var_length =  length(strip(put(var1,best.)));
  keep var1 var_length  EGSTRESN;
run;

My pupose is cpature the actual length of EGSTRESN (different muric formated varibles across many datasets, some times formated values are different) and stored in a each macro varible.

This is one scerion, for other data may not have formated value.

Thank you,

Rajasekhar.

[s_lassen]

The VVALUE function retrieves a character variable that is the formatted value of the input variable. So VAR1 is actually character, and what you get is the length of the formatted value.

You should look in your log, you probably will find a note like this

 NOTE 484-185: Format $BEST was not found or could not be loaded.

in your log for the data step, because the VAR1 variable is character. This is just a note, not an error, meaning that the character value is not changed.

So the SQL statements gets the length of the variable formatted with the BEST32. format (the length of "72" is 2), while the data step gets the length of the same value, formatted with whatever format your input variable has. If you want the same result in your SQL statement, you should change "BEST32." to whatever format the input variable actually has.

[raja777pharma]

Hi ,

Thank you for your response,

I have 50 of data sets and hundreds of variables in the same data sets, it's very difficult to know what format was used in these datasets. 

So i am looping the all data set and variables to capture the actual length, is any way to know what format was used in the dasta set for each variable  like VARTYPE and VARNUM.

Thank you,

Rajaekhar.

[s_lassen · Accepted solution]

The VVALUE solution used in the data step seems to work, you should just avoid the inner PUT statement, as your VAR1 variable is already a character value. And then you do really not need to add the VAR1 variable, instead do:

data length_ds;
  set saslib.eg(where=(not missing(EGSTRESN)));
  var_length =  length(strip(vvalue(EGSTRESN)));
  keep var_length  EGSTRESN;
run;

If you have to do it in SQL, I think you will have to write a macro to get at the format. Here is an example for a macro that generates the whole SQL expression for the variable length:

%macro formatted_length(in_data,var);
  %let var=%upcase(&var);
  %local dsid i format;
  %let dsid=%sysfunc(open(&in_data,i));
  %if &dsid=0 %then %return;
  %do i=1 %to %sysfunc(attrn(&dsid,nvars));
    %if %upcase(%sysfunc(varname(&dsid,&i)))=&var %then %do;
      %let format=%sysfunc(varfmt(&dsid,&i));
      %if %sysfunc(vartype(&dsid,&i))=N %then %do;
        %put _local_;
        %if %length(&format)=0 %then
          %let format=best32.;
        %do;length(strip(putn(&var,"&format")))%end; /* %do...%end to avoid spurious blanks in macro result */
        %end;
      %else %do;
        %if %length(&format)=0 %then 
          %do;length(strip(&var))%end;
        %else  
          %do;length(strip(putc(&var,"&format")))%end;
        %end;
      %let dsid=%sysfunc(close(&dsid));
      %return;
      %end;
    %end;
    %let dsid=%sysfunc(close(&dsid));
    %put WARNING: variable &var not found in dataset &in_data;
    %do;.%end;
%mend;

I made the macro so that it returns a missing value for the length if the variable is not on the input dataset.

You should then be able to call the macro like this:

Proc sql noprint;
create table length_ds as
select EGSTRESN, %formatted_length(saslib.eg,EGSTRESN) as var_length
from saslib.eg(where=(not missing(EGSTRESN)));
quit;

And then you may want to change the LENGTH function to LENGTHN, if you want to have 0 for the length when the format returns a blank value.