DATA Step, Macro, Functions and more

agroup and summary (using sas code not proc sql)

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

agroup and summary (using sas code not proc sql)

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)


Accepted Solutions
Solution
‎02-03-2017 03:59 PM
Super User
Posts: 17,960

Re: agroup and summary (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;

 

View solution in original post


All Replies
PROC Star
Posts: 290

Re: agroup and summary (using sas code not proc sql)

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;
Super User
Posts: 17,960

Re: agroup and summary (using sas code not proc sql)

That's the default Proc freq

 

proc freq data=have;

table state*store/out=want;

weight sales;

run;

Super User
Posts: 5,099

Re: agroup and summary (using sas code not proc sql)

And a third common approach:

 

proc summary data=have nway;

class state store;

var sales;

output out=want (keep=state store sales) sum=;

run;

Super User
Posts: 10,550

Re: agroup and summary (using sas code not proc sql)

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."

Contributor
Posts: 39

Re: agroup and summary (using sas code not proc sql)

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

Super User
Posts: 17,960

Re: agroup and summary (using sas code not proc sql)

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 Smiley Happy

 

 

Contributor
Posts: 39

Re: agroup and summary (using sas code not proc sql)

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?

Super User
Posts: 17,960

Re: agroup and summary (using sas code not proc sql)

Proc Summary or Proc Tabulate as you have more control of the output format and style.

 

Contributor
Posts: 39

Re: agroup and summary (using sas code not proc sql)

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.

Solution
‎02-03-2017 03:59 PM
Super User
Posts: 17,960

Re: agroup and summary (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;

 

Super User
Posts: 17,960

Re: agroup and summary (using sas code not proc sql)

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.  

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 205 views
  • 9 likes
  • 5 in conversation