Hi , I would be using this code very often, please suggest an alternative way to automate it
input Id $ Indi $ date: mmddyy10.;
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
create table indi-count as
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,
group by id;
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. : )
Actually my total Query is
input ID $ Indi $ date: mmddyy10.;
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
create table cont as
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
group by id;
/* my business some times require a query like this */
if Green_count> 1;
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 .
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.