Bizarre PROC SQL behavior

[tomcmacdonald]

I'm writing a series of test scripts to test a report I produce.  One of the tests I have is to see if the any row in the report is a member of another dataset.  If it is then it fails the test otherwise it passes.  I write the test by joining the report with the other dataset based on a common key and making sure the rows equal to 0.  I also have a string column which is a description of the test.  My problem is when creating this string column it appears blank when counting rows in a empty set that had a join.  For instance:

data foo;
	input a;
	datalines;
1
;
run;

data bar;
	input b;
	datalines;
2
;
run;

proc sql;
	create table baz as 
	select 'baz' as string, count(*) as n
	from foo join bar on foo.a = bar.b;
quit;

proc sql;
	create table eggs as 
	select 'eggs' as string, count(*) as n
	from foo;
quit;

proc sql;
	create table ham as 
	select 'ham' as string, count(*) as n
	from bar;
quit;

baz does not have the string column appear but it does appear for eggs and ham.  I thought it may be a problem with my SQL but I tried it out on a PostgreSQL database and am getting the expected result:

CREATE TEMPORARY TABLE foo (
	a integer
);
INSERT INTO foo VALUES (1);

CREATE TEMPORARY TABLE bar (
	b integer
);
INSERT INTO bar VALUES (2);

SELECT
	'baz' AS string, 
	COUNT(*) AS n
FROM foo JOIN bar ON foo.a = bar.b;

And the result is:

string, n
baz, 0

Any idea how I can get the string to appear in baz using PROC SQL?

[kiranv_]

Hi,

I am able to see baz 0 as shown below using proc sql

string n
baz 0

[tomcmacdonald]

Maybe it's the version I'm running?

proc setinit;
run;

Original site validation data
Current version: 9.04.01M3P062415
Operating System:   AIX 64 

[kiranv_]

May and what do you see. Mine 9.04.01M4.  try this

proc sql;

create table baz as

select 'baz' as string, coalesce(count(*),0) as n

from foo join bar on foo.a = bar.b;

quit;

[tomcmacdonald]

The result from the original code I posted is:

string, n
,0

The code you posted unfortunately doesn't work and produces the same result. 

[ballardw]

I have no problem with getting

baz, 0

for the result.

Perhaps you can post from your log the code and any notes from when you run your code that does not work.

[tomcmacdonald]

1                                                          The SAS System                                16:17 Friday, July 27, 2018

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='scratch';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH=[redacted];
6          %LET _CLIENTPROJECTNAME='scratch.egp';
7          %LET _SASPROGRAMFILE=;
8          
9          ODS _ALL_ CLOSE;
10         OPTIONS DEV=ACTIVEX;
11         GOPTIONS XPIXELS=0 YPIXELS=0;
12         FILENAME EGSR TEMP;
13         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14             STYLE=HtmlBlue
15             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16             NOGTITLE
17             NOGFOOTNOTE
18             GPATH=&sasworklocation
19             ENCODING=UTF8
20             options(rolap="on")
21         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22         
23         GOPTIONS ACCESSIBLE;
24         data foo;
25         	input a;
26         	datalines;

NOTE: Compression was disabled for data set WORK.FOO because compression overhead would increase the size of the data set.
NOTE: The data set WORK.FOO has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      
28         ;

29         run;
30         
31         data bar;
32         	input b;
33         	datalines;

NOTE: Compression was disabled for data set WORK.BAR because compression overhead would increase the size of the data set.
NOTE: The data set WORK.BAR has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      
35         ;

36         run;
37         
38         proc sql;
39         	create table baz as
40         	select 'baz' as string, count(*) as n
41         	from foo join bar on foo.a = bar.b;
NOTE: Compression was disabled for data set WORK.BAZ because compression overhead would increase the size of the data set.
NOTE: Table WORK.BAZ created, with 1 rows and 2 columns.
2                                                          The SAS System                                16:17 Friday, July 27, 2018


42         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

43         
44         proc sql;
45         	create table eggs as
46         	select 'eggs' as string, count(*) as n
47         	from foo;
NOTE: Compression was disabled for data set WORK.EGGS because compression overhead would increase the size of the data set.
NOTE: Table WORK.EGGS created, with 1 rows and 2 columns.

48         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

49         
50         proc sql;
51         	create table ham as
52         	select 'ham' as string, count(*) as n
53         	from bar;
NOTE: Compression was disabled for data set WORK.HAM because compression overhead would increase the size of the data set.
NOTE: Table WORK.HAM created, with 1 rows and 2 columns.

54         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

55         
56         GOPTIONS NOACCESSIBLE;
57         %LET _CLIENTTASKLABEL=;
58         %LET _CLIENTPROCESSFLOWNAME=;
59         %LET _CLIENTPROJECTPATH=;
60         %LET _CLIENTPROJECTNAME=;
61         %LET _SASPROGRAMFILE=;
62         
63         ;*';*";*/;quit;run;
64         ODS _ALL_ CLOSE;
65         
66         
67         QUIT; RUN;
68         

When running:

data _null_;
	set baz;
	put string;
run;

data _null_;
	set ham;
	put string;
run;

The log is:

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='scratch';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH=[redacted]
6          %LET _CLIENTPROJECTNAME='scratch.egp';
7          %LET _SASPROGRAMFILE=;
8          
9          ODS _ALL_ CLOSE;
10         OPTIONS DEV=ACTIVEX;
11         GOPTIONS XPIXELS=0 YPIXELS=0;
12         FILENAME EGSR TEMP;
13         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14             STYLE=HtmlBlue
15             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16             NOGTITLE
17             NOGFOOTNOTE
18             GPATH=&sasworklocation
19             ENCODING=UTF8
20             options(rolap="on")
21         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22         
23         GOPTIONS ACCESSIBLE;
24         data _null_;
25             set baz;
26             put string;
27         run;

NOTE: There were 1 observations read from the data set WORK.BAZ.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

28         
29         data _null_;
30             set ham;
31             put string;
32         run;

ham
NOTE: There were 1 observations read from the data set WORK.HAM.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

33         
34         GOPTIONS NOACCESSIBLE;
35         %LET _CLIENTTASKLABEL=;
36         %LET _CLIENTPROCESSFLOWNAME=;
37         %LET _CLIENTPROJECTPATH=;
38         %LET _CLIENTPROJECTNAME=;
39         %LET _SASPROGRAMFILE=;
40         
41         ;*';*";*/;quit;run;
42         ODS _ALL_ CLOSE;
2                                                          The SAS System                                16:17 Friday, July 27, 2018

43         
44         
45         QUIT; 

[ballardw]

Curious.

What do you get in the results viewer if you run the select without a create table?

proc sql;
  	select 'baz' as string, count(*) as n
  	from foo join bar on foo.a = bar.b
   ;
quit;

I was wondering if you might have been getting some message about the BAZ set not updating and a previous version was being displayed. That does not appear to be the case.

This may require a call to tech support. If others don't get the same result there is something that may be peculiar to your set up and they would be the ones likely to have tools to discover what may be going on.

I once spend about 4 hours on the phone with SAS tech support from a mystery issue: I could print and summarize existing data sets but anything that would attempt to create a dataset failed. It took that long to discover a corrupted dll that was 1 byte short in file size.

[FreelanceReinh]

@tomcmacdonald:

I can only confirm that I get the same results (in particular string=' ' in table baz) with SAS 9.4 (TS1M2) under Windows 7.