SAS PROC SQL - HOW TO PERFORM LOOP/SUMMARISATION CALCULATION BY REFERRING TO DYNAMIC TABLE

[TT_123]

Hi,

Just wondering if anyone would be able to help with the SAS PROC SQL codes please?

• Raw data sample in Table 1
• I've to summarise the amount for different Product Types (Table 2) - both at a department level (Table 3) and a section level (Table 4).  
• Both department and section lists are dynamic - they change every month.  I've to refer to these dynamic tables for summarisation.
• A sample of expected end results in (Table 5a and 5b)

Would someone know what's the SAS PROC SQL codes to summarise by referring to different dynamic tables please?

Thanks.

Raw Data sample (Table 1)

Department	Section	Product Type	Amount
Department A	Section D	101	1,000,000
Department C	Section B	105	2,000,000
Department B	Section E	102	3,000,000
Department E	Section B	103	4,000,000
Department D	Section A	104	5,000,000
Department B	Section C	102	9,000,000
Department C	Section D	101	8,000,000
Department A	Section B	103	5000,000
Department E	Section E	104	7,000,000
Department B	Section D	102	1,500,000

Table 2 - a list of products that require summarising at both a department and a section level.

Product Type

101

102

103

104

105

Table 3 - a list of latest department names (dynamic, changes each month)

Department Name

Department A

Department B

Department C

Department D

Department E

Table 4 - a list of latest section names (dynamic, changes each month)

Section Name

Section A

Section B

Section C

Section D

Section E

Table 5a - expected results, summarising by department

Department Name	Product Type	Amount
Department A	101	500,000
Department A	102	600,000
Department A	103	700,000
Department A	104	800,000
Department A	105	900,000
Department B	101	120,000
Department B	102	320,000
Department B	103	240,000
Department B	104	630,000
Department B	105	812,000

Table 5b - expected results - summarising by section

Section Name	Product Type	Amount
Section A	101	40,000
Section A	102	80,000
Section A	103	30,000
Section A	104	20,000
Section A	105	70,000
Section B	101	90,000
Section B	102	80,000
Section B	103	10,000
Section B	104	58,000
Section B	105	91,000

[PaigeMiller]

I can't imagine how you get the results where all amounts are less than 1,00,000 when the input data has all amounts in the millions. Please explain further.

Is the data dynamic and changes every month along with the department and section lists? Are there departments in the data that do not appear in the department list? Are there sections in the data that do not section in the department list?

Please do not type in ALL CAPITAL letters, as you did in the title.

[ballardw]

Why do you specify that Proc SQL has to be used?

I suspect that Proc Summary will generate the needed result in one pass. Select the appropriate _TYPE_ value if you need them split out.

Consider this example. You should have the SAS supplied data set SASHELP.CARS in your installation so the code would run and generate output.

Proc summary data=sashelp.cars;
   class   origin make type;
   var   msrp;
   output out=work.summary sum=;
run;

When you examine the output data set, WORK.SUMMARY, the summary will have the sum of the variable MSRP by overall, each level of the variables Origin, Make and Type alone and in combinations of 2 or 3 at a time the. Variable _TYPE_ can be used to subset the result as needed.

IF you only need specific values of any of the Class variables you can use a WHERE clause to restrict them but the way you asked your question is not clear whether the "lists" are specified or if you want all the values  that may change.

An additional advantage of Proc Summary over Proc SQL is if you need other variables summarized you just add the variable to the VAR statement. Also if additional statistics are needed, such as Min and Max, you can add the option /autoname to the output line and SAS will create variable names with the statistic appended. With Proc SQL you would have to write some possibly very complex code to create variable names.

Example requesting different statistics for different variables

Proc summary data=sashelp.cars;
   class   origin make type;
   var   msrp invoice horsepower weight ;
   output out=work.summary2 
       sum(msrp invoice) =  min(horsepower weight)= max(horsepower weight)=
       mean(horsepower weight)= /autoname
;
run;