Help using Base SAS procedures

Proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

Proc sql

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..


Accepted Solutions
Solution
‎11-12-2013 11:26 AM
Super User
Posts: 17,829

Re: Proc sql

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


All Replies
Trusted Advisor
Posts: 1,615

Re: Proc sql

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

Solution
‎11-12-2013 11:26 AM
Super User
Posts: 17,829

Re: Proc sql

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;

Contributor
Posts: 64

Re: Proc sql

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

Super User
Posts: 17,829

Re: Proc sql

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.

Super User
Super User
Posts: 6,500

Re: Proc sql

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

Respected Advisor
Posts: 4,649

Re: Proc sql

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
Super User
Super User
Posts: 6,500

Re: Proc sql

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?

Respected Advisor
Posts: 4,649

Re: Proc sql

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
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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