topic Re: Get distinct group wise summary in EXCEL Report using SAS in SAS Programming

Get distinct group wise summary in EXCEL Report using SAS

karthik18 — Thu, 25 Mar 2021 11:54:38 GMT

Hi Sir,

I have a data set name Have.

I want to create Excel report like mentioned in the attachment.

I have already tried Proc Report and Proc tabulate in SAS GUI option but not getting exact output as mentioned in the attachment , Please help me .

Thanks,

Karthik

Re: Get distinct group wise summary in EXCEL Report using SAS

PaigeMiller — Thu, 25 Mar 2021 11:57:25 GMT

As I stated in your other thread, many of us will not download Excel (or other) file attachments.

In order for us to help here, you would need to show us a portion of the actual data in data set HAVE (following these instructions, and not via any other method), and provide the desired output via screen capture by clicking on the "Insert Photos" icon.

Re: Get distinct group wise summary in EXCEL Report using SAS

karthik18 — Thu, 25 Mar 2021 12:53:48 GMT

Hi Sir,

Attached Data.

Re: Get distinct group wise summary in EXCEL Report using SAS

PaigeMiller — Thu, 25 Mar 2021 13:36:45 GMT

You also need to provide (a portion of) your data, using the method here and not any other method.

Re: Get distinct group wise summary in EXCEL Report using SAS

karthik18 — Thu, 25 Mar 2021 14:04:34 GMT

Hi sir i have attached detail data and expected excel report want snipit attached .

Re: Get distinct group wise summary in EXCEL Report using SAS

karthik18 — Thu, 25 Mar 2021 14:08:42 GMT

attached input data in csv format

Re: Get distinct group wise summary in EXCEL Report using SAS

Kurt_Bremser — Thu, 25 Mar 2021 14:31:13 GMT

So it seems you need to learn a valuable SAS skill first: how to create a dummy dataset for testing with DATALINES.

data have;
input name :$10. cat :$3. count_no;
datalines;
SIVA SSC 1
;

Note that I did make guesses about the lengths of your character variables, as that cannot be reliably deduced from Excel or CSV files.

This is the way example data should be presented here on the Communities (or elsewhere where you go for help).

Re: Get distinct group wise summary in EXCEL Report using SAS

karthik18 — Thu, 25 Mar 2021 15:57:08 GMT

Hi Sir,

Sorry for the miscommunication, i am trying to learn SAS base. I have created the sample data using datalines,

data outdata;
infile datalines delimiter=',';
input Name $ CAT $ Count_no;
datalines;
SIVA,SSC,1
SIVA,HSC,1
SIVA,BSC,1
SIVA,MSC,1
RAJA,SSC,1
RAJA,HSC,1
RAJA,BSC,1
GIRI,SSC,1
GIRI,HSC,1
DOR,SSC,1
RAM,SSC,1
RAM,HSC,1
SUBA,SSC,1
SUBA,HSC,1
SUBA,BSC,1
SUBA,MSC,1
MOJ,SSC,1
MOJ,HSC,1
MOJ,BSC,1
;
run;

Also, I am trying to create the summary report but summary column its not summed up into single value.

PROC TABULATE DATA=WORK.OUTDATA;
VAR Count_no;
CLASS Name / ORDER=UNFORMATTED MISSING;
CLASS CAT / ORDER=UNFORMATTED MISSING;
TABLE Name *CAT all = 'Total',(Count_no * Sum={LABEL="Sum"} );
RUN;

Re: Get distinct group wise summary in EXCEL Report using SAS

ballardw — Thu, 25 Mar 2021 16:31:48 GMT

The closest you are going to get with Proc Tabulate I think is something like this:

PROC TABULATE DATA=WORK.OUTDATA;
VAR Count_no;
CLASS Name / ORDER=UNFORMATTED MISSING;
CLASS CAT / ORDER=UNFORMATTED MISSING;
TABLE Name *(CAT all = 'Name Total') All='Overall Total',
   Count_no * Sum="Sum"*f=best5. ;
RUN;

Which will do at total for the Name, which seems to be part of your goal. However Proc Tabulate will really want to display the individual Cat sum as well because that is in the dimension.

To create some odd mix of summary and categories like that you will need to go to something like the data step Report Writing Interface which will allow you to code which rows/columns span and to output specific values in specific merged cells at the cost of learning some additional programming steps.

Re: Get distinct group wise summary in EXCEL Report using SAS

Ksharp — Fri, 26 Mar 2021 11:55:23 GMT

 

data outdata;
infile datalines delimiter=',';
input Name $ CAT $ Count_no;
datalines;
SIVA,SSC,1
SIVA,HSC,1
SIVA,BSC,1
SIVA,MSC,1
RAJA,SSC,1
RAJA,HSC,1
RAJA,BSC,1
GIRI,SSC,1
GIRI,HSC,1
DOR,SSC,1
RAM,SSC,1
RAM,HSC,1
SUBA,SSC,1
SUBA,HSC,1
SUBA,BSC,1
SUBA,MSC,1
MOJ,SSC,1
MOJ,HSC,1
MOJ,BSC,1
;
run;

proc sql;
create table want as
select name,cat,sum(count_no) as sum
 from outdata
  group by name;
quit;

proc report data=want nowd spanrows;
column name cat sum;
define name/group style={vjust=m};
define cat/display;
define sum/group 'Count No' style={vjust=m};
run;

Re: Get distinct group wise summary in EXCEL Report using SAS

karthik18 — Tue, 30 Mar 2021 11:40:20 GMT

Thanks you sir. This code is perfect.