LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (September 2004, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Fri, 24 Sep 2004 18:53:29 -0600
Reply-To:     Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Subject:      Re: group by in SQL
Comments: To: anpam@UAA.ALASKA.EDU
Content-Type: text/plain; charset=us-ascii

1) Use the DISTINCT SQL option.

2) Use the DROP= data set option.

So, combined, your program would start as create table b2a (drop=race) as select distinct

3) You could probably do it with nested case clauses, or with formats, but why bother? The code you have now is straightforward and works.

-- JackHamilton@FirstHealth.com Manager, Technical Development Metrics Department, First Health West Sacramento, California USA

>>> "Pat Moore" <anpam@UAA.ALASKA.EDU> 09/24/2004 5:34 PM >>> SAS Gurus,

Three questions:

1. When I run the code below it gives me one line for each student it finds. The counts are right, so the aggregation is working, but how do I get it down to one line per sex/ipedrace/fullpart group? Output is below program.

2. Is there a way to drop a column after you use it? I don't need the race after I calculate the ipedrace and would like it not to appear in the output table.

3. Is there a way to do the two-step ipedrace in one step and not have to calculate race in between? I chose to do it this way because I have to use values on two variables to get the ipedrace variable.

proc sql; create table B2A as select count(b.pidm) as students, case when ethnic like 'A%' then 'IN' when ethnic in ('SR','OT') then 'UN' else ethnic end as race, case when visatype in ('J1','K1') then 'NRA' else calculated race end as ipedrace, sex, case when credits ge 9 then 'FT' when 0 lt credits lt 9 then 'PT' else '' end as Fullpart from major13 a, si.fal03c1 b, si.fal03c2 c where clevel='UA' and stvmajr_code=pmajor1 and b.pidm=c.pidm group by sex,calculated ipedrace,calculated fullpart;

students race ipedrace SEX Fullpart 7 BL BL F FT 7 BL BL F FT 7 BL BL F FT 7 BL BL F FT 7 BL BL F FT 7 BL BL F FT 7 BL BL F FT 9 BL BL F PT 9 BL BL F PT 9 BL BL F PT 9 BL BL F PT 9 BL BL F PT 9 BL BL F PT 9 BL BL F PT 9 BL BL F PT 9 BL BL F PT 12 HI HI F FT 12 HI HI F FT 12 HI HI F FT 12 HI HI F FT 12 HI HI F FT 12 HI HI F FT 12 HI HI F FT 12 HI HI F FT 12 HI HI F FT 12 HI HI F FT 12 HI HI F FT 12 HI HI F FT

Thanks all.

Pat Moore


Back to: Top of message | Previous page | Main SAS-L page