Get distinct group wise summary in EXCEL Report using SAS

[karthik18]

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

[PaigeMiller]

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.

[karthik18]

Hi Sir,

Attached Data.

[PaigeMiller]

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

[karthik18]

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

[karthik18]

attached input data in csv format

[Kurt_Bremser]

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

[karthik18]

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;

[ballardw]

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.

[Ksharp · Accepted solution]

 

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;

[karthik18]

Thanks you sir. This code is perfect.