Create a Pivot table using BASE SAS

[praveen_ratna]

Dear All,

I have a data based with Payroll id , region name, margin and ctc  , i want to group the data by region name and show the sum of margin and ctc. I have used the below mentioned code but it is not working. Can anyone help me out on the same.

proc sort data=PRACTISE.ACTUALPROD19NEW;
by REGION_NAME;
run;
proc print data=PRACTISE.ACTUALPRODAPR19NEW
n='Number of observations for the PAYROLL_ID:'
'Number of observations for the data set:';
LABEL REGION_NAME='REGION NAME'
TOTAL_MARGIN='MARGIN'
CTC='CTC'
PAYROLL_ID='PAYROLL ID';
VAR REGION_NAME TOTAL_MARGIN CTC;
SUM TOTAL_MARGIN CTC;
BY REGION_NAME ;
FORMAT TOTAL_MARGIN 10.2;
run;
options byline;

[Jagadishkatam · Accepted solution]

please try the proc sql

proc sql;
create table want as select REGION_NAME, sum(REGION_NAME TOTAL_MARGIN) as REGION_NAME TOTAL_MARGIN, 
sum(CTC) as CTC from PRACTISE.ACTUALPROD19NEW group by REGION_NAME;
quit;

[praveen_ratna]

Thank you dear your response has worked for me, I want to know one more thing that can we sum the experience bucket with in the region.

Like we have four experience bucket mainly  0-6 mths, 6-12 mths , 12-24 mths and > 24 mths and i want to count the no of employees in each exp bucket in each region.

proc sql;
create table PRACTISE.PRODSUMMARY as select REGION_NAME, COUNT(PAYROLL_ID), sum(TOTAL_MARGIN) as TOTAL_MARGIN,
sum(CTC) as CTC from PRACTISE.ACTUALPRODAPR19NEW group by REGION_NAME;
quit;

Thanks.

[praveen_ratna]

Also can we do some calculation like if i need to find productivity of the region by the below mentioned formula

Productiivity = Total_margin/ctc.

sorry to bother you again, can we do this in this query.

[PaigeMiller]

---

@praveen_ratna wrote:

Dear All,

I have a data based with Payroll id , region name, margin and ctc  , i want to group the data by region name and show the sum of margin and ctc. I have used the below mentioned code but it is not working. Can anyone help me out on the same.

---

What exactly is not working?

Please provide a portion of your data using these instructions: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[Tom]

I assume by "pivot" table you mean the creation of some summary statistics?

Use PROC MEANS (aka PROC SUMMARY).

I have a data based with Payroll id , region name, margin and ctc  , i want to group the data by region name and show the sum of margin and ctc. I have used the below mentioned code but it is not working. Can anyone help me out on the same.

proc means N MIN MAX SUM data=have ;
  class  region;
  var margin ctc;
run;

[Tom]

I have a data based with Payroll id , region name, margin and ctc  , i want to group the data by region name and show the sum of margin and ctc. I have used the below mentioned code but it is not working. Can anyone help me out on the same.

proc means N MIN MAX SUM data=have ;
  class  region;
  var margin ctc;
run;

[Cynthia_sas]

Hi:

  For other suggestions, PROC TABULATE can also do crosstabular reports, sort of like a "static" pivot table. If you need a real pivot table, then investigate the ODS TAGSETS.TABLEEDITOR destination, which will create an Excel pivot table from a SAS data set.

Cynthia