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..
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;
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
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;
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
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 .
You cannot duplicate the NOTSORTED option of BY group processing with 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
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?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.