topic Re: SAS studio problems with selecting with a where clause in proc sql. in SAS Studio

SAS studio problems with selecting with a where clause in proc sql.

icrandell — Fri, 28 Feb 2020 21:30:14 GMT

I'm writing a macro to allow a user to select a subset of columns in a data table by name and then produce summaries appropriate to the type of the data in the column. The first step is to create a data set with the appropriate names to control a call execute routine. It looks like the following:

data varData;
length varnames $20 vardisplays $20;
infile datalines dlm = ",";
input varnames $ vardisplays $;
datalines;sodium, Sodium (Na);

Here, varnames will be the names of the columns in the data set to summarize and vardisplays will be a (possibly long) string to describe the variable. But, I have the following issue:

proc sql;
	select * from varData where varnames = "sodium";
quit;

This does not return any rows, even though there's a clearly a "sodium" in the varnames column. This is a problem, because I'll later need to join this data table with sashelp.vcolumn to get the data type of the column in question.

Now, this is where it gets really fun. I ran that example on sas studio version 3.8 and got the behavior I described above. I then went and ran it on the desktop version and it behaved as expected, that is, it gave me the observation.

Does anyone know what the heck is going on with sas studio here?

Re: SAS studio problems with selecting with a where clause in proc sql.

Tom — Fri, 28 Feb 2020 21:59:12 GMT

What does you SAS log say for the data step? How many lines did it read? How many observations did it write?

It does not look like you gave it any lines of data.

Your data step should look like this:

data varData;
  length varnames $20 vardisplays $20;
  infile datalines dlm = ",";
  input varnames $ vardisplays $;
datalines;
sodium, Sodium (Na)
;

Notice how the lines of data start in column 1. Notice how the data lines ending is marked by a line with a semi-colon on it. You don't need that extra RUN: statement between your data step and your PROC step. It doesn't hurt anything, other than to confuse novice SAS programmers into thinking the data step continues beyond the data lines.

Re: SAS studio problems with selecting with a where clause in proc sql.

icrandell — Fri, 28 Feb 2020 22:10:32 GMT

Tom,

my apologies, that was a copying error when I brought my code into the reply box. I did have the code spaced as you did in your reply. When I ran the data step the log reports:

74         data varData;
 75         length varnames $20. vardisplays $20.;
 76         infile datalines dlm = ",";
 77         input varnames $ vardisplays $;
 78         datalines;
 
 NOTE: The data set WORK.VARDATA has 1 observations and 2 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.01 seconds
       cpu time            0.01 seconds

The output data tab also shows a 1x2 table with the data as I entered it. When I run the proc sql afterwards, the log reports:

 74         proc sql;
 75         select * from varData where varnames = "sodium";
 NOTE: No rows were selected.
 76         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.03 seconds
       cpu time            0.03 seconds

This code also works completely as expected when I run it without modification on the desktop version of sas, which is 9.4 by the way. I can confirm that there are no syntax errors or typos of any kind. This appears to be purely SAS studio issue.

Re: SAS studio problems with selecting with a where clause in proc sql.

Tom — Fri, 28 Feb 2020 22:40:49 GMT

Make sure there are not "invisible" characters in either your data lines or your WHERE statement.

Common characters that to not print any ink on the page are:

09 - Tab

0A - Linefeed

0D - Carriage Return

A0 - "non-breaking" space

00 - null

You can use the $HEX format to see the actual codes of the characters in the strings.

Re: SAS studio problems with selecting with a where clause in proc sql.

icrandell — Mon, 02 Mar 2020 19:39:22 GMT

The hex solution did it. It turns out there was a leading tab, which I had used to align the text. Frustrating that sas studio seemed to include the leading tab where sas desktop did not.

Re: SAS studio problems with selecting with a where clause in proc sql.

Tom — Mon, 02 Mar 2020 20:16:52 GMT

You can change the editor preference to have it insert spaces instead of literal tab characters.