JSON engine: How to preserve long variable names and variable names with invalid characters

Quentin — Mon, 18 Apr 2022 21:49:45 GMT

Hi All,

I'm reading a JSON file where some variable names are >32 characters, or have characters that are not allowed to be in a SAS variable name. The JSON engine happily renames the variable names when it imports the data. I'm wondering if there is any option to have the original variable name stored in the the SAS variable label automatically? I think the EXCEL engine will do this, but I don't see a way to get the JSON engine to do it.

As a workaround, my thought is to have the JSON engine create a map with the original names of the variables, and then read that map. Something like:

options validvarname=V7 ;

filename myjson temp ;
filename mymap temp ;

data _null_;
  file myjson ;
  put '[' ;
  put  '{"Variable Unallowed Characters ?.!":1}';
  put  ',' ;
  put  '{"Really Really Really Long Variable Name #1":2, "Really Really Really Long Variable Name #2": 3}';
  put  ',' ;
  put  '{"Really Really Really Long Variable Name #1":4}';
  put ']';
run;

libname myjson json fileref=myjson  map=mymap automap=create;
libname mymap  json fileref=mymap;


data vars ;
  set mymap.datasets_variables (keep=Name Path);
  if name="ordinal_root" then delete ;
  length Label $200 ;
  Label=scan(path,-1,'/') ;
  put (Name Label)(=) ;
run ;

Which returns:

21   data vars ;
22     set mymap.datasets_variables (keep=Name Path);
23     if name="ordinal_root" then delete ;
24     length Label $200 ;
25     Label=scan(path,-1,'/') ;
26     put (Name Label)(=) ;
27   run ;

NAME=Variable_Unallowed_Characters___ Label=Variable Unallowed Characters ?.!
NAME=Really_Really_Really_Long_Variab Label=Really Really Really Long Variable Name #1
NAME=Really_Really_Really_Long_Varia2 Label=Really Really Really Long Variable Name #2
NOTE: There were 4 observations read from the data set MYMAP.DATASETS_VARIABLES.
NOTE: The data set WORK.VARS has 3 observations and 3 variables.

So I think that could work, and I could macrocize it so that when I import a JSON file, I also grab the labels via above and then run PROC DATASETS to apply them to the output dataset.

But wanted to ask if there was an easier way I'm overlooking.

Thanks,

--Q.

Re: JSON engine: How to preserve long variable names and variable names with invalid characters

ChrisNZ — Tue, 19 Apr 2022 02:59:18 GMT

I think you are on the right track.

It could be as simple as

proc sql noprint;
  select cats(NAME,'=''',scan(PATH,-1,'/'),'''n') into :labels separated by ' ' 
  from MYMAP.DATASETS_VARIABLES 
  where NAME ^= "ordinal_root";
quit;            

data WANT;
  set MYJSON.ROOT; 
  label &labels.;
run;

topic JSON engine: How to preserve long variable names and variable names with invalid characters in SAS Programming

JSON engine: How to preserve long variable names and variable names with invalid characters

Re: JSON engine: How to preserve long variable names and variable names with invalid characters