topic Re: Proc sql in SAS Procedures

Proc sql

DR_Majeti — Tue, 12 Nov 2013 15:14:36 GMT

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

Re: Proc sql

PaigeMiller — Tue, 12 Nov 2013 15:24:53 GMT

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

Re: Proc sql

Reeza — Tue, 12 Nov 2013 16:26:09 GMT

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;

Re: Proc sql

DR_Majeti — Tue, 12 Nov 2013 16:37:13 GMT

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

Re: Proc sql

Tom — Tue, 12 Nov 2013 16:51:28 GMT

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

Re: Proc sql

PGStats — Tue, 12 Nov 2013 17:51:01 GMT

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.

Re: Proc sql

Tom — Tue, 12 Nov 2013 18:01:01 GMT

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?

Re: Proc sql

PGStats — Tue, 12 Nov 2013 18:23:42 GMT

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

Re: Proc sql

Reeza — Tue, 12 Nov 2013 18:31:18 GMT

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 .