BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DR_Majeti
Quartz | Level 8

Hi all

                                                                                                                      
proc freq data=sasuser.flightdelays noprint;                                                                                      
                                                                                                                            

by date notsorted;                                                                                                           
                                                                                                                                  
  tables origin*destinationtype /out=delay;                                                                                       
                                                                                                                                  
run;   

how to re-write this same code with using proc sql;

please help me out to write the code... i want to learn proc sql in easy way to.

Thank you all in advance..

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

I disagree, this is a simple first step in learning proc sql, how to count by groups.

Let me know if the following matches what you were looking for:

proc sql;

create table delay as

select date, origin, destinationtype, count(*) as freq

from sasuser.flightdelays

group by date, origin, destinationtype

order by date, origin, destinationtype;

quit;

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

Since PROC SQL and PROC FREQ do fundamentally different things, I'm not sure you can do this in PROC SQL in an "easy way"; although you might be able to write some complicated code to do this.

The question is why learn PROC SQL in this manner by matching PROC FREQ? Seems to me you would be missing the main features of PROC SQL that can be very powerful and very useful. I'm sure there are better ways to learn PROC SQL, for example: http://www2.sas.com/proceedings/sugi29/268-29.pdf

--
Paige Miller
Reeza
Super User

I disagree, this is a simple first step in learning proc sql, how to count by groups.

Let me know if the following matches what you were looking for:

proc sql;

create table delay as

select date, origin, destinationtype, count(*) as freq

from sasuser.flightdelays

group by date, origin, destinationtype

order by date, origin, destinationtype;

quit;

DR_Majeti
Quartz | Level 8

Hi Reeza,

Thank you for the reply, can we calculate the percentages in the same step?

data set :                                             Output dataset:                                    

value   count                                         value     count

a              58                                             a                58    

b               42                                            b               42

                                                                   z             100 ------>  This is the sum of  above two observations..

I want to do it in sql procedure...

Thank you..

Durga

Reeza
Super User

You can calculate percentages but if you're looking for totals, then you're best off going with a reporting procedure rather than SQL. SQL is more a data manipulation/prep proc in my opinion.

Part of being a good programmer is using the right tool for the right job Smiley Happy.

Tom
Super User Tom
Super User

You cannot duplicate the NOTSORTED option of BY group processing with SQL.

PGStats
Opal | Level 21

For once, I disagree with Tom. What you cannot duplicate with SQL is the FIRST, LAST and LAG features of the datastep. SQL never assumes a given ordering, its processing is skillfully conceived as non sequential (except for the non-supported monotonic() function, may it stay that way). SQL ALWAYS assumes that groups are NONSORTED. Moreover, it always assumes that observations are not grouped in any way.

As a rule : if observations ordering is essential to your processing then implement it as a SAS datastep. Otherwise, congratulations, your data structure is robust enough for datastep AND SQL processing.

PG

PG
Tom
Super User Tom
Super User

If I have ID variables ordered 1,1,2,2,1,1 and process it BY ID NOTSORTED I will get three groups.

How can I get the same result with PROC SQL?

PGStats
Opal | Level 21

You cannot of course, as your processing depends on observations ordering. But then, why aren't these ID=1 observations together? Isn't there some variable missing here (DATE perhaps)? I am not arguing against the usefulness of the datastep, I simply consider its use as an indication of poorly structured information. Maybe it is a case of having a hammer and whishing every thing was shaped as a nail...

PG

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1182 views
  • 4 likes
  • 5 in conversation