topic Re: Why does this GROUP BY statement fail? in New SAS User

Why does this GROUP BY statement fail?

Syntas_error — Mon, 25 Mar 2019 09:35:41 GMT

I'm working through the advanced programmer prep guide, double checking the examples on the way to make sure I undestand.

The book (i think) states that if we type AVG(variable1) and then GROUP BY(variable2) we will get one average of Variable1 for each value of Variable2.

I submit the folloinw code, (createing the raw data)

Data rawdata;
input v1 v2;
datalines;
2 3
2 4
6 4 
8 9
2 4
;
run;

I then perform the operations in PROC SQL, substituting AVG() for MEAN() since AVG(( isn't part of base SAS and thus can't be used in PROC SQL:

PROC SQL;
SELECT mean(v2) as v3
FROM rawdata
GROUP BY v1;
quit;

Why does this fail to execute?

As fas as I can see, four values should be generated. One for each distinct value of v1 and an average ((3+4/)2)when v1=2.

Re: Why does this GROUP BY statement fail?

Amir — Mon, 25 Mar 2019 10:24:40 GMT

Please share the full log with any messages, e.g.:

13         Data rawdata;
14         input v1 v2;
15         datalines;

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

21         ;
22         run;
23         
24         PROC SQL;
25         SELECT mean(v2) as v3
26         FROM rawdata
27         GROUP BY v1;
28         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.02 seconds

Please clarify what you mean by "fails to execute".

Try selecting v1 in your select statement to see if that makes a difference.

Amir.

Re: Why does this GROUP BY statement fail?

Syntas_error — Mon, 25 Mar 2019 10:48:39 GMT

There's really nothing in th log to write home about, I tried including var1 in the SELECT statement, yielding exactly the same result:

297 Data rawdata;
298 input v1 v2;
299 datalines;
NOTE: The data set WORK.RAWDATA has 5 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

305 ;
306 run;
307
308 PROC SQL FEEDBACK;
309 SELECT mean(v2) as v3
310 FROM rawdata
311 GROUP BY v1;
NOTE: Statement transforms to:
select MEAN(RAWDATA.v2) as v3
from WORK.RAWDATA
group by RAWDATA.v1;
312 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.05 seconds
cpu time 0.03 seconds

Re: Why does this GROUP BY statement fail?

Syntas_error — Mon, 25 Mar 2019 10:50:57 GMT

Here's the log with v1 included:

313 PROC SQL FEEDBACK;
314 SELECT v1, mean(v2) as v3
315 FROM rawdata
316 GROUP BY v1;
NOTE: Statement transforms to:
select RAWDATA.v1, MEAN(RAWDATA.v2) as v3
from WORK.RAWDATA
group by RAWDATA.v1;
317 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.06 seconds
cpu time 0.03 seconds

Re: Why does this GROUP BY statement fail?

Syntas_error — Mon, 25 Mar 2019 10:51:58 GMT

When I say fails to execute I say that in a figurative sense, e.g "nothing happens", "no output is produced".

Re: Why does this GROUP BY statement fail?

RW9 — Mon, 25 Mar 2019 12:00:06 GMT

What output do you want? The default - so long as you haven't turned the destination off - for SQL output is the list file (could be called listing). You should be able to see it in there. If you want a dataset, then you need the create table statement:

proc sql;
  create table want as 
  select ...
quit;

So a dataset called want (in this case) will be created with the results of the query.

Re: Why does this GROUP BY statement fail?

Syntas_error — Mon, 25 Mar 2019 12:05:46 GMT

I would settle for standard output or any output at all really, but none is produced?

Re: Why does this GROUP BY statement fail?

RW9 — Mon, 25 Mar 2019 12:12:37 GMT

Then you must have turned off the listing destination with something like:

ods listing close;

or 

ods _all_ close;

Print output from procedures default to listing destination, if thats closed you wouldn't get anything. You can do:

ods listing;

Before your code to ensure it is on.

Re: Why does this GROUP BY statement fail?

Syntas_error — Mon, 25 Mar 2019 12:22:22 GMT

No. I haven't worked with the ods system today. Also, this statement works
flawlessly ("and" produces output) as soon as the GROUP BY statement is
removed.

This has nothing to do with ODS.

Re: Why does this GROUP BY statement fail?

RW9 — Mon, 25 Mar 2019 12:30:54 GMT

I am afraid all I can see here is "This works" "this does not", no examples or anything. Let me put it this way, in a SAS session I just put the code:

Data rawdata;
input v1 v2;
datalines;
2 3
2 4
6 4 
8 9
2 4
;
run;
PROC SQL;
SELECT mean(v2) as v3
FROM rawdata
GROUP BY v1;
quit;

Which gives the log:

289        Data rawdata;
290        input v1 v2;
291        datalines;

NOTE: The data set WORK.RAWDATA has 5 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

297        ;
298        run;
299        PROC SQL;
300        SELECT mean(v2) as v3
301        FROM rawdata
302        GROUP BY v1;
303        quit;
NOTE: The PROCEDURE SQL printed page 2.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

And produces in the listing file the output;

/**********************************************************************************************************************
 * Submission Start: Untitled.sas*
 * Mar 25, 2019 12:29:01 GMT by ***
 *********************************************************************************************************************/
                                          The SAS System          12:29 Monday, March 25, 2019   2

                                                   v3
                                             --------
                                             3.666667
                                                    4
                                                    9

/**********************************************************************************************************************
 * Submission End: Untitled.sas*
 * Mar 25, 2019 12:29:01 GMT
 *********************************************************************************************************************/

So the code does "work" and produces the correct output, so what is it different about your output, what is "not working", where is it "not working", what is "supposed to happen" which is not? If your not getting listing output then that destination has been turned off.

Re: Why does this GROUP BY statement fail?

Syntas_error — Mon, 25 Mar 2019 13:46:40 GMT

I see now there was an issue with my "Result viewer" window, which resolved itself when I restarted SAS. I will mark this as the answer since it indicated the problem wasn't with my code byt with my current SAS session.

Re: Why does this GROUP BY statement fail?

RW9 — Mon, 25 Mar 2019 14:05:18 GMT

Not too sound too much like IT, "Have you turned it off and on again". I would assume ods destinations had been closed, hence no output.

Re: Why does this GROUP BY statement fail?

ballardw — Mon, 25 Mar 2019 14:32:14 GMT

@Syntas_error wrote:

I see now there was an issue with my "Result viewer" window, which resolved itself when I restarted SAS. I will mark this as the answer since it indicated the problem wasn't with my code byt with my current SAS session.

Since your example log did not show a No output destinations active message I would be interested to see if you ever checked the OUTPUT window, not just the Results.

If something has turned off ODS HTML but ODS LIsting is active there is no message in the log but the results of your SQL (or any other procedure) would go to the LISTING destination, i.e. the OUTPUT window.