topic Re: Can we use a where clause in a proc summary? in SAS Programming

Proc summary syntax

GN0001 — Fri, 24 Mar 2023 22:49:23 GMT

Hello team,

I can't fix this and it keeps giving me errors. I have worked on this for a long time.

proc summary nway missing data=somename.allofthem;
(where = (MBR_STATE="CA"));
var cnt pos neg;
class Mbr_State;
output out=result_by_state (drop=_TYPE_ _FREQ_) sum=;
run;

2970       proc summary nway missing data=somename.allofthem;
2971        (where = (MBR_STATE="CA"));
            _
            180
170                                                        The SAS System                               14:38 Friday, March 24, 2023

ERROR 180-322: Statement is not valid or it is used out of proper order.
2972         var cnt pos neg;
2973         class Mbr_State;
2974         output out=result_by_state (drop=_TYPE_ _FREQ_) sum=;
2975       run;

Regards,

blue

Re: Proc summary syntax

WarrenKuhfeld — Fri, 24 Mar 2023 23:40:21 GMT

Your where clause is a data set option. So delete the semicolon in front of it.

Re: Proc summary syntax

Tom — Sat, 25 Mar 2023 00:45:46 GMT

Make up your mind.

Either use the WHERE= dataset option.

proc summary nway missing data=somename.allofthem(where = (MBR_STATE="CA"));

Or use a WHERE statement.

proc summary nway missing data=somename.allofthem;
 where MBR_STATE="CA";

Re: Proc summary syntax

GN0001 — Sun, 26 Mar 2023 17:52:39 GMT

Hello,
It is a where clause. The second one gives errors.
What does data option do? Doesn’t bring same result as where clause?

Re: Proc summary syntax

GN0001 — Sun, 26 Mar 2023 17:55:54 GMT

proc summary nway missing data=somename.allofthem;
 where MBR_STATE="CA";

this gives errors.

Re: Proc summary syntax

Kurt_Bremser — Sun, 26 Mar 2023 19:26:08 GMT

Data Set Options

Re: Proc summary syntax

ballardw — Sun, 26 Mar 2023 19:39:00 GMT

@GN0001 wrote:

proc summary nway missing data=somename.allofthem;
 where MBR_STATE="CA";

this gives errors.

How many times do we have to recommend that when you have errors that you copy the code with the error text and paste it into a text box?

Re: Proc summary syntax

GN0001 — Sun, 26 Mar 2023 20:32:31 GMT

Hello,

I placed it in a text box. It is in a grey text box. Or I can’t understand what you mean.

I took out semicolon from proc summary and the log still shows that I have placed semicolon after proc summary,

This is what I have:
Proc summary….
Var…….;
Class;
Where….;
Run;
The log shows that I have ; after proc summary while I deleted ;

Re: Proc summary syntax

Kurt_Bremser — Sun, 26 Mar 2023 21:03:33 GMT

Open the log window. Mark the complete text from the beginning of the procedure to the final message. Copy it to the clipboard. Open a window here with the </> button. Paste the clipboard contents into it.

Can we use a where clause in a proc summary?

GN0001 — Sun, 26 Mar 2023 23:27:52 GMT

proc summary data=sashelp.class
where Age = 12;
class sex;
var Height;
output out=class sum=sum;
run;

proc print data=class;
run;
data test1;
set sashelp.class;
run;

The result that I get from this program doesn't seem correct. I assume proc summary can't take a where clause. Just I would like to gain confirmation.

Thanks

a peach on a tree!

Re: Can we use a where clause in a proc summary?

SASKiwi — Sun, 26 Mar 2023 23:44:31 GMT

Yes you can. Correct your syntax error (missing semicolon):

proc summary data=sashelp.class;
where Age = 12;
class sex;
var Height;
output out=class sum=sum;
run;

SAS 101: SAS statements start with a key word and end in a semicolon.

Re: Can we use a where clause in a proc summary?

Tom — Mon, 27 Mar 2023 01:21:30 GMT

You can use a where STATEMENT with PROC SUMMARY, just like with almost every other PROC.

But you forgot to end your PROC statement before you started the WHERE statement.

proc summary data=sashelp.class;
  where Age = 12;
  class sex;
  var Height;
  output out=class sum=sum;
run;

If you never end the PROC statement then is going to try to treat WHERE and AGE as additional syntax on the PROC SUMMARY statement. Neither WHERE or AGE are valid parts of the PROC SUMMARY statement.

Re: Can we use a where clause in a proc summary?

GN0001 — Mon, 27 Mar 2023 03:27:41 GMT

Hello,

thanks for the response. I put semicolon after proc summary, it gives error, really I have changed the location of where and started placing semicolon after proc and then I took it, but no success.

I have be been doing this for three days and no success.
Originally the code was like this:

proc summary ….;

var…..;

class….;

where….;

run;

I was running this code for more than two years but all of sudden the word were changed to red and I change the place of where and it edited the code as below:

proc summary….;

where….;

var….;

clas…..;

run;

no success

I am going crazy!

any tips?
thanks,

blue

Re: Can we use a where clause in a proc summary?

Kurt_Bremser — Mon, 27 Mar 2023 05:57:38 GMT

POST.

THE.

LOG.

Re: Can we use a where clause in a proc summary?

Kurt_Bremser — Mon, 27 Mar 2023 06:02:11 GMT

And do not start a new thread for the same issue. It is really disturbing that we have to tell you such things again and again; in fact, you're not acting respectfully towards us.

Re: Can we use a where clause in a proc summary?

GN0001 — Mon, 27 Mar 2023 06:31:16 GMT

Hello,

I have not started the new thread for same issue. I can’t print log because of phi and if I do so, that would be considered crime and it has jail time.

thanks for understanding!

I placed it in a dataset from sashelp that every one can make it.

blue

Re: Proc summary syntax

GN0001 — Mon, 27 Mar 2023 06:32:50 GMT

Please go to very first post. You will see that I placed the log.
Thanks
Blue

Re: Can we use a where clause in a proc summary?

GN0001 — Mon, 27 Mar 2023 06:34:42 GMT

Please kindly go the first post and you will see the log!

Re: Proc summary syntax

Kurt_Bremser — Mon, 27 Mar 2023 06:36:17 GMT

@GN0001 wrote:
Please go to very first post. You will see that I placed the log.
Thanks
Blue

You were told where the mistake in that code was. Since you still get an ERROR, we need to see the log from the current code you submit.

Re: Proc summary syntax

GN0001 — Mon, 27 Mar 2023 06:36:43 GMT

I have copied the code and place it in a text box. His is what I see.