How can I agroup in a summary if i have this obs:
STATE STORE SALES
AL A 1
AL A 2
AL B 1
AK C 2
AK C 2
AZ D 1
AZ D 1
AZ E 0
AZ E 5
AR F 1
AR F 1
The result I want is:
STATE STORE SALES
AL A 3
AL B 1
AK C 4
AZ D 2
AZ E 5
AR F 2
(using sas code not proc sql)
You should try these yourself still. Also, work through a paper perhaps, this is a good one here:
http://www2.sas.com/proceedings/sugi29/240-29.pdf
Here's some sample code based on SASHELP.SHOES data set since I find it hard to write code without a data set to check the results. The concept is the same, N is for count, Sum is the sum and mean is average. Check the documentation for the other statistics you can calculate.
Proc means is typically used to calculate stats and proc tabulate is more for displaying results in a specified format, ie developing reports.
proc means data=sashelp.shoes nway;
class region product subsidiary;
output out=want n=Count sum(sales)=Total_Sales;
run;
proc print data=want (drop =_type_ _freq_ obs=10);
run;
Try an approach like this. It's not tested, but you should be able to make it work.
proc sort;
by state store;
run;
data want;
retain sum;
set have;
by state store;
if first.store then sum = sales;
else sum = sum + sales;
if last.store then output;
run;
That's the default Proc freq
proc freq data=have;
table state*store/out=want;
weight sales;
run;
And a third common approach:
proc summary data=have nway;
class state store;
var sales;
output out=want (keep=state store sales) sum=;
run;
Please define what you may mean by "SAS Code" as Proc SQL is SAS code. You obviously have something in mind but you really should clarify.
If your process cannot use Proc SQL please explain why. There are some things that Proc SQL is designed to do such as a many-to-many merge that could take a lot of other coding to accomplish. There are cases where this type of request is similar to "I need to excavate for a building foundation but cannot use the backhoe that is sitting on the property but must use a table spoon."
Hello ballardw you always help me with my questions, I prefer use sas code not proc sql because some process I know how do it with proc sql, but I want to learn how do it with code sas pure
PROC SQL is 'pure sas', whatever that means.
I think what you're trying to say, is you'd like to learn using data step and procs such as summary/univariate rather than proc sql. Saying that PROC SQL isn't 'SAS' would be incorrect and it's just as useful and in fact can be the easiest way to accomplish certain tasks. Personally, I find all joins are easier for me to manage via SQL, especially since many to many merges aren't handled as expected in a data step.
I think your questions been answered, so please mark it solved with the correct solution selected 🙂
I agree with you Reeza you too always help me with my questions, Im just starting to use sas Im newbie sorry if my questions are too basics other problem is that I dont know english very well my lenguage is spanish and is difficult for me ask and explain in english my questions well thank you for your patience, in other way for agroup I have 2 solutions:
1 proc freq
2 proc summary
what do you thing is better?
Proc Summary or Proc Tabulate as you have more control of the output format and style.
Thanks Reeza for example cold you please explain how do this proc sql to proc summary and proc tabulate?
PROC SQL;
SELECT STATE, STORE, PRODUCT, Count(NSALES) AS SALES
FROM BIGSHOP
GROUP BY STATE, STORE, PRODUCT;
quit;
I think if you help me to convert this PROC SQL to proc summary and proc tabulate everything is clear for me and the future.
You should try these yourself still. Also, work through a paper perhaps, this is a good one here:
http://www2.sas.com/proceedings/sugi29/240-29.pdf
Here's some sample code based on SASHELP.SHOES data set since I find it hard to write code without a data set to check the results. The concept is the same, N is for count, Sum is the sum and mean is average. Check the documentation for the other statistics you can calculate.
Proc means is typically used to calculate stats and proc tabulate is more for displaying results in a specified format, ie developing reports.
proc means data=sashelp.shoes nway;
class region product subsidiary;
output out=want n=Count sum(sales)=Total_Sales;
run;
proc print data=want (drop =_type_ _freq_ obs=10);
run;
There's nothing to apologize for as well, ask your questions regardless of how basic. Unfortunately in programming terminology can be important so it's good to identify specific key words that don't make sense, ie pure sas.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.