Proc Sort Followed by PROC PRINT, confusion on why I am not getting uniform results

[moseland]

I am learning SAS with the SAS essentials learning module that has been very helpful thus far. One of the practice questions requries we produce the following code:

proc sort data=pg1.storm_summary out=STORM_SORT;
where Basin IN ("NA" "na");
by descending MaxWindMPH;
run;

I understand that the above code is creating an output file called STORM_SORT that has certain filters. Based ont he log I got ~400 rows returned.  I'm trying to dig deeper with my understanding and tried a PROC PRINT step  that tries to print the same filtered information as before.

Proc print data=pg1.storm_summary;
where Basin IN ("NA" "na");
by descending MaxWindMPH;
run;

When I run it only 1 row is returned. I am stumped as to what my error is. Please help point me in the right direction and thank you! 

[Reeza]

---

@moseland wrote:

I am learning SAS with the SAS essentials learning module that has been very helpful thus far. One of the practice questions requries we produce the following code:

proc sort data=pg1.storm_summary out=STORM_SORT;
where Basin IN ("NA" "na");
by descending MaxWindMPH;
run;

I understand that the above code is creating an output file called STORM_SORT that has certain filters. Based ont he log I got ~400 rows returned.  I'm trying to dig deeper with my understanding and tried a PROC PRINT step  that tries to print the same filtered information as before.

Proc print data=pg1.storm_summary;
where Basin IN ("NA" "na");
by descending MaxWindMPH;
run;

When I run it only 1 row is returned. I am stumped as to what my error is. Please help point me in the right direction and thank you! 

---

Read your log. 

I'm expecting to see an error regarding the BY statement, since it's not sorted you cannot use that as the BY statement. 

[moseland]

You were right! When i check the log I get the following explanation:

Data set PG1.STORM_SUMMARY is not sorted in descending sequence. The current BY group has MaxWindMPH = ' 35'
and the next BY group has MaxWindMPH = ' 190'. 

What I don't understand is the variable MaxWindMPH has many other values other than 35 and 190. Would I need to sort the entire data set by descending order first  and then sort again by the variable I want, MaxWindMPH? 

[SASKiwi]

Change your PROC PRINT step to use the sorted dataset, rather than your unsorted one, and it will work perfectly:

Proc print data=storm_sort;
where Basin IN ("NA" "na");
by descending MaxWindMPH;
run;

[RW9]

You need to look at what the procedure in question is doing, and what it is for.

Proc sort - this procedure is for sorting data.

Proc print - this procedure is for printing data.

You do not use proc print to sort, hence when you use the by statement in a proc print, it assumes the data is already sorted, and as it is not then your procedure fails.

Unlike SQL (and other languages) data going into procedures is expected to be sorted when using by group processing, if it isn't then you will get warnings/errors.  If you add the option notsorted, then you will get output, but it will block the data in the order it finds it, which is often not the way intended, so use cautiously.

So, if your problem is, I want to sort the data, then print it out:
1) proc sort out=sorted

2) proc print data=sorted

[Reeza]

---

@moseland wrote:

You were right! When i check the log I get the following explanation:

Data set PG1.STORM_SUMMARY is not sorted in descending sequence. The current BY group has MaxWindMPH = ' 35'
and the next BY group has MaxWindMPH = ' 190'. 

What I don't understand is the variable MaxWindMPH has many other values other than 35 and 190. Would I need to sort the entire data set by descending order first  and then sort again by the variable I want, MaxWindMPH? 

---

No, once it's sorted it's fine, even if filtered because the sort order will still be correct, assuming you use a data step. If you're using SQL there is no guarantee of order. But your PROC PRINT is referencing the original unsorted data set, not your sorted data set.

[moseland]

This concept now makes sense. Thank you!