topic Re: agroup and summary (using sas code not proc sql) in SAS Programming

agroup and summary (using sas code not proc sql)

Angel_Saenz — Thu, 02 Feb 2017 01:19:08 GMT

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)

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

collinelliot — Thu, 02 Feb 2017 01:25:29 GMT

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;

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

Reeza — Thu, 02 Feb 2017 02:25:52 GMT

That's the default Proc freq

proc freq data=have;

table state*store/out=want;

weight sales;

run;

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

Astounding — Thu, 02 Feb 2017 07:37:01 GMT

And a third common approach:

proc summary data=have nway;

class state store;

var sales;

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

run;

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

ballardw — Thu, 02 Feb 2017 16:16:56 GMT

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

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

Angel_Saenz — Thu, 02 Feb 2017 22:41:55 GMT

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

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

Reeza — Thu, 02 Feb 2017 22:55:39 GMT

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 🙂

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

Angel_Saenz — Thu, 02 Feb 2017 23:04:43 GMT

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?

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

Reeza — Thu, 02 Feb 2017 23:12:10 GMT

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

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

Reeza — Thu, 02 Feb 2017 23:16:38 GMT

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.

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

Angel_Saenz — Fri, 03 Feb 2017 17:35:27 GMT

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.

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

Reeza — Fri, 03 Feb 2017 20:34:52 GMT

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;