Can we use a where clause in a proc summary?

[Kurt_Bremser]

POST.

THE.

LOG.

[GN0001]

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

[Tom]

 the word were changed to red

Are you talking about the syntax highlighting in the editor?  I would not worry about the syntax highlighting if the code runs.  The editor is just trying to help you, but it cannot really know what SAS is going to do when you submit the code.

Also if valid syntax suddenly is getting errors messages (or the editor is highlighting if funny) then probably you have a missing semicolon or unclosed quote or unclosed parentheses earlier in the code.  Go back to the first error and check.

If the editor is not flagging anything and you still get some errors then perhaps you need to re-start the SAS session that is running the code to make sure you have cleared any mismatched quotes, etc.

[Kurt_Bremser]

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.

[GN0001]

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

[SASKiwi]

I note PHI stands for Protected Health Information (that's what I think you mean - correct me if I am wrong).

Now what dataset are you using in your posted program? SASHELP.CLASS.

This is a public dataset provided by SAS with all SAS installations and has nothing to do with PHI.

Just reproduce your latest coding problem with SASHELP.CLASS and post the SAS log. I can promise you there is no jail time for that.

[GN0001]

Hello team,

PHI = protected health information

This is what the code edited to and it worked. I placed it here for future users. The problem was that "Where" turned to red all of a sudden. I changed the location of where and deleted semicolon from proc summary line. 

This is the code:

 proc summary nway missing data=path.members
(where = (MBR_STATE="CA"));

This is the log:
2976       
2977       proc summary nway missing data=path.members
2978        (where = (MBR_STATE="CA"));
2979         var a b c
2980         class zip;
2981         output out=thisdata (drop=_TYPE_ _FREQ_) sum=;
2982       run;

Thanks, 

Blue 

[SASKiwi]

Your VAR statement is missing a semicolon:

var a b c;

[Tom]

If that is the log then the problem is somewhere BEFORE line 2976 because otherwise there would be messages after the RUN; statemen showing how many observations read and how many written.

Does your dataset really have a variable named CLASS that you want to include in the list of variables for PROC SUMMARY?

Are the variables CLASS and ZIP also numeric?  If not you cannot take the SUM of them.

[Tom]

It is best practice to write only one statement per line of code.  And if the statement is really long (for example a SELECT statement in PROC SQL code) then place the semicolon that ends the statement on a new line.  

Doing this with your code makes it much clearer where you likely made a mistake.

So your log shows you ran this code:

proc summary nway missing data=path.members
  (where = (MBR_STATE="CA"))
;
   var a b c
     class zip
   ;
   output out=thisdata (drop=_TYPE_ _FREQ_) sum=;
run;

I suspect you intended to run this code:

proc summary nway missing data=path.members(where = (MBR_STATE="CA"));
   var a b c ;
   class zip ;
   output out=thisdata (drop=_TYPE_ _FREQ_) sum=;
run;

[ballardw]

---

@GN0001 wrote:

Hello team,

PHI = protected health information

This is what the code edited to and it worked. I placed it here for future users. The problem was that "Where" turned to red all of a sudden. I changed the location of where and deleted semicolon from proc summary line. 

This is the code:

 proc summary nway missing data=path.members
(where = (MBR_STATE="CA"));

This is the log:
2976       
2977       proc summary nway missing data=path.members
2978        (where = (MBR_STATE="CA"));
2979         var a b c
2980         class zip;
2981         output out=thisdata (drop=_TYPE_ _FREQ_) sum=;
2982       run;

Thanks, 

Blue 

---

Not quite. It does not include the notes about the completion of the step, or not, and how many observations are in the output data set.

When I replace your data set with SASHELP.CLASS and use variables from that set in the same syntax this is the result:

364  proc summary nway missing data=sashelp.class
365   (where = (age>13));
366    var height weight
367    class sex;
ERROR: Variable CLASS not found.
ERROR: Variable Sex in list does not match type prescribed for this list.
368    output out=thisdata (drop=_TYPE_ _FREQ_) sum=;
369  run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.THISDATA may be incomplete.  When this step was stopped there were 0
         observations and 0 variables.
WARNING: Data set WORK.THISDATA was not replaced because this step was stopped.
NOTE: PROCEDURE SUMMARY used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Note the ERROR message prior to CLASS because Class is not a variable in the data set. Your code should show something different if you do not have a variable named Class in your path.members data set.

If I correct that with the ; in the correct place at the end of the VAR statement:

370  proc summary nway missing data=sashelp.class
371   (where = (age>13));
372    var height weight;
373    class sex;
374    output out=thisdata (drop=_TYPE_ _FREQ_) sum=;
375  run;

NOTE: There were 9 observations read from the data set SASHELP.CLASS.
      WHERE age>13;
NOTE: The data set WORK.THISDATA has 2 observations and 3 variables.
NOTE: PROCEDURE SUMMARY used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

The NOTEs at the end of the code are important and often answers questions. Such as "why is my output set empty". READ the log and you might set something about 0 observations read from the data set where the condition is stated. Happens all the time when we misspell a word or miss the case of a value "jane" instead of "Jane" for example.

So show the notes after the procedure.

IF there are not any, and that can happen, then save your code and restart your SAS session. The most common cause of no details in the log is creating an imbalanced quote or ( ) pair so that the syntax parser thinks your posted code is part of something else and you have likely made the current SAS session unstable. Especially if you have submitted multiple instance of incorrect code.

[Kurt_Bremser]

Data Set Options