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.
... View more
