Uninitialized heading variable when using sashelp.vcolumn to grab data and put to export CSVs?

[Callmesnacks]

Hi Sas folks, 

My team and I have built up a dataset that ultimately gets exported as individual CSV flat files based on the value of a filter variable. The code is as follows (Thanks @Reeza!)

%let lib_name = work;
%let dsn_name = XYZ;
*Variable to split on;
%let var_split = SECUR_LBL_CODE;
*path to folder to save text files;
%let path_folder= //data/dev/myname/;


PROC SORT DATA=&lib_name..&dsn_name OUT=_temp;
BY &var_split;
RUN;


proc sql noprint;
select name into :var_list_csv separated by ", " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
select name into :var_list separated by " " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
quit;

DATA _NULL_;

SET _temp; 
BY &var_split.; 


if first.&var_split. then out_file=cats("&path_folder.", &var_split., "_MedicareSegData.csv");

file temp filevar=out_file dlm=',' dsd;


if first.&var_split. then 
put "&var_list_csv.";


put &var_list.;

run;  

This code works flawlessly except for a single wrinkle which we've only just identified. We used that code to export data that contains a field beginning with a numerical value (in this case 90_day_rate). If the code is run, it doesn't error out, but rather, after taking a look at the log, the only thing out of the ordinary is that it says "variable _DAY_RATE is uninitialized" (interestingly, not 90_day_rate). Oddly enough, the output is labeled correctly, but all of the data after and including the 90 day rate field seems to shift at random. This is why it took so long to catch!

Looking at the log, I applied a hotfix by adding a non numerical character to the field's name. Unfortunately, we use this data as part of a much larger data model (in another system) that relies on template headings to ingest information, and it'd be a big a lift to rerun and load the data over again from scratch. 

With that said, does anyone know of a way that we can modify the existing code somewhat to allow for the 90_day_rate field without causing havoc to the resulting export? 

Thanks in advance for any assistance, 

-Snacks

[KANDIPATI]

May be you can use OPTIONS VALIDVARNAME=ANY;

[Reeza]

Can you post the full note?

90_day_rate isn't a default SAS valid variable name so you must have validvarname set to something else?
Can you illustrate exactly how you call the program when it fails?

[Reeza]

May be as simple as adding NLITERAL to the NAME selection:

proc sql noprint;
select name into :var_list_csv separated by ", " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
select nliteral(name) into :var_list separated by " " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
quit;

[Tom]

Just fix how you are generating the variable list so that it will create valid SAS code.

You can also simplify the code while you are at it.

proc sql noprint;
select name
     , nliteral(name)
 into :var_list_csv separated by "," 
    , :var_list separated by " " 
from sashelp.vcolumn
where libname = %upcase("&lib_name")
  and memname = %upcase("&dsn_name")
;
quit;

data _null_;
  set _temp; 
  by &var_split.; 
  out_file=cats("&path_folder.", &var_split., "_MedicareSegData.csv");
  file temp filevar=out_file dsd;
  if first.&var_split. then put "&var_list_csv.";
  put &var_list.;
run;  

[Callmesnacks]

Thanks!

I'll try these possible solutions and get back to you ASAP. 

[Callmesnacks]

Hey Everyone, 

Thanks for the assistance! We tried each of the solutions independently and unfortunately, each had their own emergent error. That said, it helped us identify a way to hardcode in a solution. 

Initially, the application of nliteral(name) to any part of the code caused the following error:

put "&var_list_csv.";
NOTE: Line generated by the macro variable "VAR_LIST_CSV".
65          "BUILD_HEDIS_YR, BUILD_CODE, SECUR_LBL_CODE, RPTG_LOB, GRP_ID, GRP_NAME, QLTY_RATING_OVRL_SCORE, PANEL_SIZE, ! "90_DAY_RATE"N, P4Q_PTNTL_MAX_AMT
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.

ERROR 22-322: Syntax error, expecting one of the following: a name, #, +, @.  

However, that indicated that the issue was because the 90_day_rate field was already being passed into sashelp.vcolumn as a literal, and was incorrectly being parsed into two separate fields (with the _day_rate field being unitialized). 

With that in mind, we hardcoded in a solution to check for that specific issue, and throw an exception which forced sashelp.vcolumn to use the entire field. This (while being inelegant) allowed us to keep the existing format, and properly initialize all of the variables. 

Ultimately, we swapped out this: 

proc sql noprint;
select name into :var_list_csv separated by ", " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
select name into :var_list separated by " " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
quit;

for this:

proc sql noprint;
select case when substr(name,1,2)="90" then cats("'",name,"'n") else name end into :var_list_csv separated by ", " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
select case when substr(name,1,2)="90" then cats("'",name,"'n") else name end into :var_list separated by " " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
quit;

Either way, couldn't have figured it out without learning about the Nliteral(name) function! 

Thanks!

[Tom]

*** This is wrong if you are pulling the values of the NAME from SASHELP.VCOLUMN as those names are NOT stored as name literals  ***  Might be useful if you had some other source of names that were in the form of name literals.

You should be able to fix that with DEQUOTE() function. 

proc sql noprint;
select name
     , dequote(name)
 into :var_list separated by " " 
    , :var_list_csv separated by "," 
from sashelp.vcolumn
where libname = %upcase("&lib_name")
  and memname = %upcase("&dsn_name")
;
quit;

(PS Why are you still running the query twice?  You can just use commas to select multiple values and direct them into different macro variables from a single query.  Besides simplifying the code it also eliminates the risk that the name and column headers will get out of synch because PROC SQL retrieved the values in different orders in the two independent queries.)

You might also want protect against commas or quotes in the name literal values.

proc sql noprint;
select name
     , case when indexc(dequote(name),''',"') then quote(dequote(name))
            else dequote(name) end
 into :var_list separated by " " 
    , :var_list_csv separated by "," 
from sashelp.vcolumn
where libname = %upcase("&lib_name")
  and memname = %upcase("&dsn_name")
;
quit;
....
if first..... then put %sysfunc(quote(%superq(var_list_csv)));
..

[Callmesnacks]

Thanks Tom,

I'll definitely try that out, anything I can do to avoid hardcoding a workaround is always welcome.

Regarding why I'm running things twice, I'm still pretty new at explaining my code, but if I'm not mistaken, the first run (var_list) populates the values, and var_list_CSV populates the headings. The end result is a bunch of CSVs that we load into a Power-bi or powerpivot data model (hence why maintaining the formatting is so crucial).

[Callmesnacks]

Hey Tom,

Gave both code snippets a try, but unfortunately I'm still running into the original issue (variable _Day_rate is uninitialized) and one I'm also getting a new warning message indicating At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.

See below:

NOTE: Variable _DAY_RATE is uninitialized.
NOTE: The file TEMP is:
Filename=//data/dev/xxxx

The minimum record length was 96.
The maximum record length was 129.
NOTE: There were 16603 observations read from the data set WORK._TEMP.
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.
NOTE: DATA statement used (Total process time):

[Reeza]

That's odd, this works perfectly fine for me:

option validvarname=any;
data have;
input SplitVar '90_day_variable'n other_Variable;
cards;
1 8 5
1 9 5 
1 0 4
2 5 9
3 4 6
;;;;
run;


%let lib_name = work;
%let dsn_name = HAVE;
*Variable to split on;
%let var_split = SPLITVAR;
*path to folder to save text files;
%let path_folder= /home/fkhurshed/Demo1/Reports/;


PROC SORT DATA=&lib_name..&dsn_name OUT=_temp;
BY &var_split;
RUN;


proc sql noprint;
select name into :var_list_csv separated by ", " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
select nliteral(name) into :var_list separated by " " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
quit;

DATA _NULL_;

SET _temp; 
BY &var_split.; 


if first.&var_split. then out_file=cats("&path_folder.", &var_split., "_MedicareSegData.csv");

file temp filevar=out_file dlm=',' dsd;


if first.&var_split. then 
put "&var_list_csv.";


put &var_list.;

run;  

[Tom]

You wouldn't run two queries to print two variables.  So don't run two queries to create two macro variables.

It is like used:

proc print;
  var name;
run;
proc print;
  var label;
run;

Instead of 

proc print;
  var name label;
run;

To debug the issue create the macro variable content and then write it to the log so you can review that it is going to be valid for generating the PUT statements you need.

For example if you created macro variables named VAR_LIST and VAR_LIST_CSV you could run these two %PUT statements to show what they contain.

%put &=var_list;
%put &=var_list_csv;

The VAR_LIST variable needs to be a space delimited list of variable names that you could use in a PUT or VAR statement.

The VAR_LIST_CSV variable needs to be something that you can use in a PUT statement to write a string to file. It should have the header text separated by commas. 

[Reeza]

This shows you applied the NLITERAL to both the var_list_csv and var_list macro variable lists. That is incorrect, it should be only the var_list macro. 

The var_list_csv is in quotes for the header so the NLITERAL is not required there.

The var_list is used for the PUT statement and does need the NLITERAL to have it quoted properly so SAS realizes it's a variable. 

This is based on the assumption that you do not want it quoted in the header column, which is the standard output.

---

@Callmesnacks wrote:

Hey Everyone, 

Thanks for the assistance! We tried each of the solutions independently and unfortunately, each had their own emergent error. That said, it helped us identify a way to hardcode in a solution. 

Initially, the application of nliteral(name) to any part of the code caused the following error:

put "&var_list_csv.";
NOTE: Line generated by the macro variable "VAR_LIST_CSV".
65          "BUILD_HEDIS_YR, BUILD_CODE, SECUR_LBL_CODE, RPTG_LOB, GRP_ID, GRP_NAME, QLTY_RATING_OVRL_SCORE, PANEL_SIZE, ! "90_DAY_RATE"N, P4Q_PTNTL_MAX_AMT
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.

ERROR 22-322: Syntax error, expecting one of the following: a name, #, +, @.  

However, that indicated that the issue was because the 90_day_rate field was already being passed into sashelp.vcolumn as a literal, and was incorrectly being parsed into two separate fields (with the _day_rate field being unitialized). 

With that in mind, we hardcoded in a solution to check for that specific issue, and throw an exception which forced sashelp.vcolumn to use the entire field. This (while being inelegant) allowed us to keep the existing format, and properly initialize all of the variables. 

Ultimately, we swapped out this: 

proc sql noprint;
select name into :var_list_csv separated by ", " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
select name into :var_list separated by " " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
quit;

for this:

proc sql noprint;
select case when substr(name,1,2)="90" then cats("'",name,"'n") else name end into :var_list_csv separated by ", " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
select case when substr(name,1,2)="90" then cats("'",name,"'n") else name end into :var_list separated by " " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
quit;

Either way, couldn't have figured it out without learning about the Nliteral(name) function! 

Thanks!

---

[Kurt_Bremser]

Use the NLITERAL function when pulling the names for the PUT statement:

proc sql noprint;
select name into :var_list_csv separated by ", " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
select nliteral(name) into :var_list separated by " " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
quit;

[Callmesnacks]

Perfect, that actually solved a second issue that we were having. We used the workaround code for :Var_List and the original code for :Var_List_CSV, that resulted in the accurate final product!

Thanks again Reeza!