topic Re: Create a Pivot table using BASE SAS in New SAS User

Create a Pivot table using BASE SAS

praveen_ratna — Sat, 11 May 2019 05:15:00 GMT

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;

Re: Create a Pivot table using BASE SAS

Jagadishkatam — Sat, 11 May 2019 07:15:46 GMT

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;

Re: Create a Pivot table using BASE SAS

PaigeMiller — Sat, 11 May 2019 10:38:53 GMT

@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-data-AKA-generate/ta-p/258712

Re: Create a Pivot table using BASE SAS

Tom — Sat, 11 May 2019 16:31:21 GMT

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;

Re: Create a Pivot table using BASE SAS

Cynthia_sas — Sat, 11 May 2019 17:13:15 GMT

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

Re: Create a Pivot table using BASE SAS

praveen_ratna — Mon, 13 May 2019 04:57:05 GMT

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.

Re: Create a Pivot table using BASE SAS

praveen_ratna — Mon, 13 May 2019 05:17:22 GMT

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.