topic Re: get variable name from vcolumn in SAS Programming

get variable name from vcolumn

stataq — Wed, 16 Oct 2024 14:17:33 GMT

Hello,

I would like to get the variables that contain "_raw" in my folder. I tried to utilized vcolumn but got following error message. Could anyone told me how to work around it?

data varlst;
	set sashelp.vcolumn;
 if  libname = 'RAW' and index(name,'_RAW') ; 
	keep memname name label;
run;

ERROR: Some character data was lost during transcoding in the dataset RAW.XXX. Either the data contains characters that are not representable in the new encoding or truncation occurred during transcoding.
ERROR: SQL View SASHELP.VCOLUMN could not be processed.

Re: get variable name from vcolumn

Kurt_Bremser — Wed, 16 Oct 2024 14:51:42 GMT

You have a dataset with an encoding different from your session encoding.

You need to switch to a SAS session with an encoding matching that of the dataset.

Re: get variable name from vcolumn

stataq — Wed, 16 Oct 2024 15:46:30 GMT

@Kurt_Bremser It looks like my SAS is using WLATIN1 and the datasets used UTF-8. What should I do to overcome this difference?

Thanks.

Re: get variable name from vcolumn

Tom — Wed, 16 Oct 2024 15:53:02 GMT

Not sure why a query of SASHELP.VCOLUMN would cause SAS to check any of the values of any variables in any of the datasets. Is it possible you have some VIEWS that might be referencing the RAW.XXX dataset so that trying the get the metadata about which variables are in the dataset caused SAS to actually try to read the RAW.XXX dataset?

You could try using a WHERE statement instead of an IF condition to filter the observations before they make it into the data step. But note that the SASHELP views will not honor WHERE conditions, so SASHELP.VCOLUMN will still try to query every open libref, not just the RAW libref.

So try using DICTIONARY.COLUMNS directly and see if that helps any.

proc sql;
create table varlst as
  select memname,name,label
  from dictionary.columns
  where libname = 'RAW' 
    and index(upcase(name),'_RAW')
 ; 
quit;

Re: get variable name from vcolumn

Kurt_Bremser — Wed, 16 Oct 2024 15:53:40 GMT

Use a SAS session configured for UTF-8. How you do this depends on your SAS setup (local or client/server). When server-based, you need to consult your SAS admin.

Re: get variable name from vcolumn

Kurt_Bremser — Wed, 16 Oct 2024 15:57:56 GMT

The problem is: to get the names of the variables, the DICTIONARY.COLUMNS pseudo-table has to read at least the header of each dataset.

It might even be that someone was crazy enough to use a UTF character in a name literal.

Re: get variable name from vcolumn

stataq — Wed, 16 Oct 2024 16:12:18 GMT

Using "where' instead of 'if' doesn't change the situation. 😅

I am using sas server and probably wont be able to change my session setup. Is it a way to convert dataset so I can still read it?

Re: get variable name from vcolumn

Tom — Wed, 16 Oct 2024 16:40:48 GMT

Are you asking how to read a dataset with UTF-8 codes that cannot translate into the single byte encoding that your current SAS session is using? (A different question than getting list of variable names from SAS metadata.)

You can use the ENCODING='ANY' dataset option to get the values into your data step.

data want;
  set raw.xxx(encoding='any');

At that point you can try to craft your own logic to find the characters that cannot be transcoded and change them to something else. For example what if you have the UTF-8 code for uppercase Greek Delta character?

data test;
  length _raw $4;
  _raw='CE94'x;
run;

What character(s) in WLATIN1 would you want to translate 'CE94'x into?

You might try replacing it with the word Delta, but make sure that the variable is long enough to store the extra 3 bytes needed.

data want;
  length _raw $10 ;
  set test(encoding='any');
  _raw=tranwrd(_raw,'CE94'x,'Delta');
run;

proc print;
run;

Re: get variable name from vcolumn

stataq — Wed, 16 Oct 2024 20:39:11 GMT

@Tom I would still like to get the variable list from data, but I feel something in 'Label' cause the issue. 😅

Re: get variable name from vcolumn

Kurt_Bremser — Wed, 16 Oct 2024 21:07:27 GMT

As an end user in a server-based environment, you can't change the session encoding, but the SAS administrator can provide one for you.

And you will need one to read that particular (and probably a lot others) dataset.

Keep in mind that someone was able to create that dataset in your organisation, so there must be a UTF-encoded workspace server available.