Proc SQL Missing Fields in Temp Table, but not in SAS Output

[JimHarrison]

[edited to clarify the output differences]

SAS Server v9.4

EG v7.15 HF8 x64

Win10 Enterprise v1903 x64

I'm using proc sql to (eventually) build a TSV that's properly-formatted for submission to the National Student ClearingHouse (NSC).

When it writes the query results to SAS output, all the fields I specify are there:

Term | Acad Plan | Acad Sub Plan | Degree | NSC data field ID | NSC SSAN | First Name | MI | Last Name | Suffix | Bday | NSC Search Date | NSC blank field | NSC School code | NSC Branch Code | NSC Req Rtn Fld

But when I write the results to work.temp so I can proc export it to tsv, I get the following results

Term | Acad Plan | Acad Sub Plan | Degree | <MISSING> | <MISSING> | <MISSING> | <MISSING> | <MISSING> | <MISSING> | Bday | NSC Search Date | <MISSING> | <MISSING> | <MISSING> | NSC Req Rtn Fld

fields.

I thought it might have been because of my use of empty field assignments , but even if I assign them static text, those fields are still missing.

Thx,

Jim

[JimHarrison · Accepted solution]

A coworker helped me resolve this.

There seems to be some inconsistency (or I don't yet understand the vagaries of proc sql syntax).

For the missing fields, I have to assign the label as

<field selection> as 'label'n

..but not for others.

What's interesting is that where I successfully assign labels using

<selection> 'label'

Specifying them using

<selection> as 'label'n

results in seeing the label of the original field selection

[Tom]

In SAS there is a difference between the NAME of the variable and the LABEL assigned to the variable.  In SQL if you just list a string after the field definition then it is taken as a label.  But if you add the AS keyword then it is taken as a name.  You can still add a label after the name.  You can use the LABEL= keyword to help clarify which you mean.

449   proc sql;
450   create table xx as
451   select age
452        , name 'Label for printing'
453        , sex as Gender
454        , weight as WT 'Used be called WEIGHT'
455        , height as HT label='was HEIGHT'
456   from sashelp.class(obs=3)
457   ;
NOTE: Table WORK.XX created, with 3 rows and 5 columns.

458   describe table xx;
NOTE: SQL table WORK.XX was created like:

create table WORK.XX( bufsize=65536 )
  (
   Age num,
   Name char(8) label='Label for printing',
   Gender char(1),
   WT num label='Used be called WEIGHT',
   HT num label='was HEIGHT'
  );

459   quit;

Normal variable names must only contain letters, digits or underscores and must not start with a digit.  This allows the parser to understand what is a variable name and what is a number of other token in your code.

You can relax these rules (but why would you want to?) be setting the option VALIDVARNAME to ANY.  If you have done that then you can use the strange names for your variables, but in your code you still need to do something that allows the parser to read your code.  In that case you can use name literals.  A quoted string with an N suffixed.  In PROC SQL you also have the option of setting the option DQUOTE=ANSI in the PROC SQL statement. In that case any string in bounded by double quote characters will be treated as a name instead of a string literal.