Summay Data by Proc Sql --- Easy Question. I think my braind
in [SAS]
|
From: Sigurd Hermansen on 15 Sep 2009 15:44 I'd say an easy question if you use PROC FREQ and know the correct parameter values, but not so easy in SAS SQL: PROC SQL; select distinct t1.ID as ID,t1.TYPE as TYPE, coalesce(t2.CNT,0) as CNT from (select * from (select distinct ID from aa),(select distinct TYPE from aa)) as t1 left join (SELECT distinct ID,TYPE,COUNT(ID) as CNT from aa GROUP BY ID,TYPE ) as t2 on t1.ID=t2.ID and t1.TYPE=t2.TYPE ; QUIT; S -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of pigpigpig Sent: Tuesday, September 15, 2009 2:12 PM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Summay Data by Proc Sql --- Easy Question. I think my braind dies today I think it should be a easy question. My brain dies today. I have a dataset as follow: There are totally 2 claim types. Data aa; input ID $2. +1 TYPE $3.; cards; 99 LTR 99 LTR 99 STR 00 STR 00 STR 00 STR ; RUN; PROC PRINT DATA=aa; run; PROC SQL; SELECT ID, TYPE, COUNT(ID) as CNT from aa GROUP BY ID, TYPE; QUIT; run; The SAS System 09:20 Tuesday, September 15, 2009 4 ID TYPE CNT ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 00 STR 3 99 LTR 2 99 STR 1 ID=00 doesn't have any claim type=LTR, Is there a quick way to let the output also shows 00 LTR 0 00 STR 3 99 LTR 2 99 STR 1 By the way, I am hoping this can be done in one sql. I don't want extra merge step or data step.
From: Amar Mundankar on 16 Sep 2009 07:47 On Sep 16, 12:44 am, HERMA...(a)WESTAT.COM (Sigurd Hermansen) wrote: > I'd say an easy question if you use PROC FREQ and know the correct parameter values, but not so easy in SAS SQL: > > PROC SQL; >   select distinct t1.ID as ID,t1.TYPE as TYPE, coalesce(t2.CNT,0) as CNT >     from (select * from (select distinct ID from aa),(select distinct TYPE from aa)) as t1 >   left join >   (SELECT distinct ID,TYPE,COUNT(ID) as CNT >    from aa >    GROUP BY ID,TYPE >      ) as t2 >      on t1.ID=t2.ID and t1.TYPE=t2.TYPE >      ; >  QUIT; > > S > > -----Original Message----- > From: SAS(r) Discussion [mailto:SA...(a)LISTSERV.UGA.EDU] On Behalf Of pigpigpig > Sent: Tuesday, September 15, 2009 2:12 PM > To: SA...(a)LISTSERV.UGA.EDU > Subject: Summay Data by Proc Sql --- Easy Question. I think my braind dies today > > I think it should be a easy question. My brain dies today. > I have a dataset as follow: > > There are totally 2 claim types. > > Data aa; > input  ID $2. +1 TYPE $3.; > cards; > 99 LTR > 99 LTR > 99 STR > 00 STR > 00 STR > 00 STR > ; > RUN; > > PROC PRINT DATA=aa; > run; > > PROC SQL; > SELECT ID, TYPE, COUNT(ID) as CNT from aa > GROUP BY ID, TYPE; > QUIT; > run; > >     The SAS System           09:20 Tuesday, September 15, > 2009  4 > >                            ID  TYPE > CNT > > ÆÆÆÆÆÆÆÆÆÆÆÆÆÆÆÆÆÆ >                            00  STR > 3 >                            99  LTR > 2 >                            99  STR > 1 > > ID=00 doesn't have any claim type=LTR, > Is there a quick way to let the output also shows >                             00 > LTR      0 >                            00  STR > 3 >                            99 > LTR      2 >                            99  STR > 1 > > By the way, I am hoping this can be done in one sql. I don't want > extra merge step or data step. Hi, Modified version of my earlier code is as below. PROC SQL; SELECT temp2.id,temp2.type,coalesce(temp1.cnt,0) FROM (SELECT DISTINCT aa.id, a2.type FROM aa , aa AS a2 ) as temp2 left join (SELECT ID, TYPE, COUNT(ID) as CNT from aa GROUP BY ID, TYPE) as temp1 on temp2.id = temp1.id and temp2.type = temp1.type; quit; Thanks and Regards, Amar Mundankar.
|
Pages: 1 Prev: Possible to create a folder (in Windows) from SAS? Next: Invalid argument to function QUOTE |