BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Angel_Saenz
Quartz | Level 8

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

11 REPLIES 11
collinelliot
Barite | Level 11

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;
Reeza
Super User

That's the default Proc freq

 

proc freq data=have;

table state*store/out=want;

weight sales;

run;

Astounding
PROC Star

And a third common approach:

 

proc summary data=have nway;

class state store;

var sales;

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

run;

ballardw
Super User

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

Angel_Saenz
Quartz | Level 8

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

Reeza
Super User

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 🙂

 

 

Angel_Saenz
Quartz | Level 8

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?

Reeza
Super User

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

 

Angel_Saenz
Quartz | Level 8

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.

Reeza
Super User

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;

 

Reeza
Super User

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.  

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 11 replies
  • 1091 views
  • 9 likes
  • 5 in conversation