SAS code Question!
in [SAS]
|
Prev: software engineering
Next: font question
From: tianrui sun on 5 Nov 2009 20:40 Hello , Anyone can help? I have a question about one sas coding problem. Currently, have a data with 4 attributes, year, type, C and rate. I want to use PROC SQL to find out the smallest 2 rate in type group for each year. For instance, there have a data set like this. year type, C, rate. 1998 A ms 10 1998 A mt 30 1998 B ml 40 1998 B mu 50 1998 B mu 60 1998 C ms 70 1998 C md 80 1997 A md 500 1997 A md 600 1997 A sp 700 1997 B sf 800 1997 B sd 100 1997 C sc 200 1997 D sf 300 1997 D sr 400 in 1998 type A I want the smallest 2 rate rows also in 1998 type B I want the smallest 2 rate rows also in 1997 type A I want the smallest 2 rate as well. the result like below: Year type C rate 1998 A ms 10 1998 A mf 20 1998 B ml 40 1998 B mu 50 1998 C ms 70 1998 C md 80 1997 A md 500 1997 A md 600 1997 B sf 100 1997 B sd 800 1997 C sc 200 1997 D sf 300 1997 D sr 400 How can I do it in SAS code?
From: Arthur Tabachneck on 5 Nov 2009 23:43 I really don't like recommending undocumented functions, but it's the most direct way I can think of to do this with proc sql: data have; input year type $ C $ rate; cards; 1998 A ms 10 1998 A mt 30 1998 B ml 40 1998 B mu 50 1998 B mu 60 1998 C ms 70 1998 C md 80 1997 A md 500 1997 A md 600 1997 A sp 700 1997 B sf 800 1997 B sd 100 1997 C sc 200 1997 D sf 300 1997 D sr 400 ; proc sql noprint; create table want as select *,monotonic() as x from have group by year,type having x le min(x)+1 order by year descending,type,rate ; quit; HTH, Art --------- On Nov 5, 8:40 pm, tianrui sun <tianrui...(a)gmail.com> wrote: > Hello , Anyone can help? > > I have a question about one sas coding problem. Currently, have a data > with 4 attributes, year, type, C and rate. I want to use PROC SQL to > find out the smallest 2 rate in type group for each year. > > For instance, there have a data set like this. > year type, C, rate. > 1998 A ms 10 > 1998 A mt 30 > 1998 B ml 40 > 1998 B mu 50 > 1998 B mu 60 > 1998 C ms 70 > 1998 C md 80 > 1997 A md 500 > 1997 A md 600 > 1997 A sp 700 > 1997 B sf 800 > 1997 B sd 100 > 1997 C sc 200 > 1997 D sf 300 > 1997 D sr 400 > > in 1998 type A I want the smallest 2 rate rows also in 1998 type B I > want the smallest 2 rate rows also in 1997 type A I want the smallest > 2 rate as well. > > the result like below: > Year type C rate > 1998 A ms 10 > 1998 A mf 20 > 1998 B ml 40 > 1998 B mu 50 > 1998 C ms 70 > 1998 C md 80 > 1997 A md 500 > 1997 A md 600 > 1997 B sf 100 > 1997 B sd 800 > 1997 C sc 200 > 1997 D sf 300 > 1997 D sr 400 > > How can I do it in SAS code?
From: Gerhard Hellriegel on 6 Nov 2009 06:07 there are also ways without SQL. One possible: proc sort data=have out=w; by descending year type rate; run; data w; set w; by descending year type rate; if first.type then cc=0; cc+1; if cc<=2; run; (only with documented features) Gerhard On Thu, 5 Nov 2009 20:43:41 -0800, Arthur Tabachneck <art297(a)NETSCAPE.NET> wrote: >I really don't like recommending undocumented functions, but it's the >most direct way I can think of to do this with proc sql: > >data have; > input year type $ C $ rate; > cards; >1998 A ms 10 >1998 A mt 30 >1998 B ml 40 >1998 B mu 50 >1998 B mu 60 >1998 C ms 70 >1998 C md 80 >1997 A md 500 >1997 A md 600 >1997 A sp 700 >1997 B sf 800 >1997 B sd 100 >1997 C sc 200 >1997 D sf 300 >1997 D sr 400 >; > >proc sql noprint; > create table want as > select *,monotonic() as x from have > group by year,type > having x le min(x)+1 > order by year descending,type,rate >; >quit; > >HTH, >Art >--------- >On Nov 5, 8:40 pm, tianrui sun <tianrui...(a)gmail.com> wrote: >> Hello , Anyone can help? >> >> I have a question about one sas coding problem. Currently, have a data >> with 4 attributes, year, type, C and rate. I want to use PROC SQL to >> find out the smallest 2 rate in type group for each year. >> >> For instance, there have a data set like this. >> year type, C, rate. >> 1998 A ms 10 >> 1998 A mt 30 >> 1998 B ml 40 >> 1998 B mu 50 >> 1998 B mu 60 >> 1998 C ms 70 >> 1998 C md 80 >> 1997 A md 500 >> 1997 A md 600 >> 1997 A sp 700 >> 1997 B sf 800 >> 1997 B sd 100 >> 1997 C sc 200 >> 1997 D sf 300 >> 1997 D sr 400 >> >> in 1998 type A I want the smallest 2 rate rows also in 1998 type B I >> want the smallest 2 rate rows also in 1997 type A I want the smallest >> 2 rate as well. >> >> the result like below: >> Year type C rate >> 1998 A ms 10 >> 1998 A mf 20 >> 1998 B ml 40 >> 1998 B mu 50 >> 1998 C ms 70 >> 1998 C md 80 >> 1997 A md 500 >> 1997 A md 600 >> 1997 B sf 100 >> 1997 B sd 800 >> 1997 C sc 200 >> 1997 D sf 300 >> 1997 D sr 400 >> >> How can I do it in SAS code?
From: Arthur Tabachneck on 6 Nov 2009 09:44 Gerhard, I don't think this was a double post but, rather, one of the effects of the delayed appearance of Google posts. Art ------- On Fri, 6 Nov 2009 09:40:36 -0500, Gerhard Hellriegel <gerhard.hellriegel(a)T-ONLINE.DE> wrote: >there are already several answers to that. Please follow the original >thread and do not post the same thing twice! > >Gerhard > > > > >On Thu, 5 Nov 2009 17:40:58 -0800, tianrui sun <tianruisun(a)GMAIL.COM> >wrote: > >>Hello , Anyone can help? >> >>I have a question about one sas coding problem. Currently, have a data >>with 4 attributes, year, type, C and rate. I want to use PROC SQL to >>find out the smallest 2 rate in type group for each year. >> >>For instance, there have a data set like this. >>year type, C, rate. >>1998 A ms 10 >>1998 A mt 30 >>1998 B ml 40 >>1998 B mu 50 >>1998 B mu 60 >>1998 C ms 70 >>1998 C md 80 >>1997 A md 500 >>1997 A md 600 >>1997 A sp 700 >>1997 B sf 800 >>1997 B sd 100 >>1997 C sc 200 >>1997 D sf 300 >>1997 D sr 400 >> >> >>in 1998 type A I want the smallest 2 rate rows also in 1998 type B I >>want the smallest 2 rate rows also in 1997 type A I want the smallest >>2 rate as well. >> >>the result like below: >>Year type C rate >>1998 A ms 10 >>1998 A mf 20 >>1998 B ml 40 >>1998 B mu 50 >>1998 C ms 70 >>1998 C md 80 >>1997 A md 500 >>1997 A md 600 >>1997 B sf 100 >>1997 B sd 800 >>1997 C sc 200 >>1997 D sf 300 >>1997 D sr 400 >> >>How can I do it in SAS code?
From: Gerhard Hellriegel on 6 Nov 2009 09:40
there are already several answers to that. Please follow the original thread and do not post the same thing twice! Gerhard On Thu, 5 Nov 2009 17:40:58 -0800, tianrui sun <tianruisun(a)GMAIL.COM> wrote: >Hello , Anyone can help? > >I have a question about one sas coding problem. Currently, have a data >with 4 attributes, year, type, C and rate. I want to use PROC SQL to >find out the smallest 2 rate in type group for each year. > >For instance, there have a data set like this. >year type, C, rate. >1998 A ms 10 >1998 A mt 30 >1998 B ml 40 >1998 B mu 50 >1998 B mu 60 >1998 C ms 70 >1998 C md 80 >1997 A md 500 >1997 A md 600 >1997 A sp 700 >1997 B sf 800 >1997 B sd 100 >1997 C sc 200 >1997 D sf 300 >1997 D sr 400 > > >in 1998 type A I want the smallest 2 rate rows also in 1998 type B I >want the smallest 2 rate rows also in 1997 type A I want the smallest >2 rate as well. > >the result like below: >Year type C rate >1998 A ms 10 >1998 A mf 20 >1998 B ml 40 >1998 B mu 50 >1998 C ms 70 >1998 C md 80 >1997 A md 500 >1997 A md 600 >1997 B sf 100 >1997 B sd 800 >1997 C sc 200 >1997 D sf 300 >1997 D sr 400 > >How can I do it in SAS code? |