BookmarkSubscribeRSS Feed
nxmogil
Fluorite | Level 6

Hi , I would be using this code very often, please suggest an alternative way to automate it

 

Data cont;

input Id $ Indi $ date: mmddyy10.;

datalines;

001 green 02/23/2022

002  green 03/21/2022

001 black 04/23/2022

002 breen 06/18/2002

003 brown 05/19/2022

004 Black 08/15/2000

;

run;

 

proc sql;

create table indi-count as 

select id, 

sum(case when Indi = "green" then 1 else 0 end) as green_count,

sum(case when Indi = "black" then 1 else 0 end) as black_count,

sum(case when Indi = "brown" then 1 else 0 end) as brown_count,

from cont

group by id;

quit;

5 REPLIES 5
Quentin
Super User

Do you need the wide format (one record per ID), or do you only need the counts?  I would start with PROC FREQ:

 

proc freq data=cont ;
  tables id*indi/missing out=want ;
run ;

proc print data=want ;
run ;

This has the benefit of catching all the typos in the data too. : )

BASUG is hosting free webinars ! Next up: Art Carpenter on February 28 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
nxmogil
Fluorite | Level 6

Thank you for your quick response
Yeah I use proc freq some times to but when I want to give a condition that if green_count > 0 , Can I do it with oroc freq results
PaigeMiller
Diamond | Level 26

@nxmogil wrote:

Yeah I use proc freq some times to but when I want to give a condition that if green_count > 0 , Can I do it with oroc freq results

I don't understand this. Please explain further, provide more details.

--
Paige Miller
nxmogil
Fluorite | Level 6

Actually my total Query is 

 

data cont;
input ID $ Indi $ date: mmddyy10.;
datalines;
001 Green 02/23/2001
001 Black 02/24/2001
001 Green 03/18/2000
002 Green 01/19/2021
002 Green 02/19/2012
003 Red 01/11/2000
004 Black 02/12/2021
;
run;

proc sql;
create table cont as
select ID,
sum(case when Indi ="Green" then 1 else 0 end ) as Green_count,
sum(case when Indi ="Red" then 1 else 0 end ) as Red_count,
sum(case when Indi ="Black" then 1 else 0 end ) as Black_count,
sum(case when Indi ="Orange" then 1 else 0 end ) as Orange_count
from cont
group by id;
quit;

/* my business some times require a query like this */

data check;
set cont;
by id;
if Green_count> 1;
run;

 

My Question here is like how I am using if condition from the sql results , can i also query the same from proc freq results . 

Quentin
Super User

@nxmogil wrote:

Thank you for your quick response
Yeah I use proc freq some times to but when I want to give a condition that if green_count > 0 , Can I do it with oroc freq results

Yes, you could do:

 

proc freq data=cont ;
  tables id*indi/missing out=want ;
run ;

data want2 ;
  set want ;
  if indi='green' and count>0 ;
run ;

But for your sample data that would give you same list of IDs as:

data want;
  set cont;
  if Indi='green';
run;

I think it would be helpful if you describe more of the big picture of what you are trying to do.  Also, suggest fixing the typos in your sample code to provide example code that runs without errors, and the example output you want.

BASUG is hosting free webinars ! Next up: Art Carpenter on February 28 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 191 views
  • 0 likes
  • 3 in conversation