topic Report dataset to Excel in specific format. in SAS Programming

Report dataset to Excel in specific format.

SASuserlot — Wed, 24 Feb 2021 00:17:45 GMT

I am trying to achieve specific format from dataset ( Sashelp.cars) only using Asia origin reporting into excel sheet in the format mentioned in the Image. how I can achieve this, is there any obstacles to achieve in this format?. Any suggestions greatly appreciated

@Cynthia_sas I saw you given so many solutions for Excel output in the past. I really appreciate if you can help with this.

Re: Export dataset to Excel in specific format.

Tom — Wed, 24 Feb 2021 00:14:47 GMT

That is not an EXPORT. That is a REPORT.

Use a reporting tool like PROC REPORT or perhaps PROC TABULATE with ODS EXCEL output destination.

Re: Report dataset to Excel in specific format.

SASuserlot — Wed, 24 Feb 2021 00:19:33 GMT

Thanks Tom, Sorry for the confusion. I corrected it. Yes I do understand it can be done by proc report, but I am kind of stuck how to achieve the horizontal headers with in the headers part.

Re: Report dataset to Excel in specific format.

Tom — Wed, 24 Feb 2021 00:22:49 GMT

The process works a lot better when you show what code you have tried and explain it what ways it does not meet your needs.

Re: Report dataset to Excel in specific format.

SASuserlot — Wed, 24 Feb 2021 01:04:32 GMT

I am kind of new to SAS. I tried the basic code, Please guide.

data cars;
set sashelp.cars;
where origin="Asia" and Make = "Acura" and type= "Sedan";
styl= "Drive train";
run;
ods ESCAPECHAR = '^';
ods excel file='C:\Users\xxu\Desktop\xxr\cars.xlsx' options(sheet_name = "Asia"
 sheet_interval='none');


   PROC REPORT data= cars nowindows
					      style(report)=[font=("Times New Roman", 11pt) protectspecialchars=on]
					      style(header)=[font=("Times New Roman", 11pt, bold) just=center protectspecialchars=on]
					      style(column)=[font=("Times New Roman", 11pt) protectspecialchars=on];
      COLUMN  origin make  styl  type   drivetrain model cylinders wheelbase mpg_city enginesize Horsepower;
      DEFINE origin / order  NOPRINT style=[font=("Times New Roman",11pt)just=c vjust=t vjust=t cellwidth=2.745cm tagattr='type:String format:Text'];
      DEFINE make /  order  "make" display style=[just=l vjust=t cellwidth=2cm tagattr='type:String format:Text'];
      DEFINE styl/  "style" display style=[just=c vjust=t cellwidth=3.882cm tagattr='type:String format:Text'];
      DEFINE type/  order  "type" display style=[just=c vjust=t cellwidth=8.882cm tagattr='type:String format:Text'];
      DEFINE drivetrain/"drivetrain" display style=[just=c vjust=t cellwidth=2.718cm tagattr='type:String format:Text'];
      DEFINE model /"model" display style=[just=c vjust=t cellwidth=2.818cm tagattr='type:String format:Text'];
      DEFINE Cylinders /"cylinders" display style=[just=c vjust=t cellwidth=3.212cm tagattr='type:String format:Text'];
      DEFINE wheelbase /"wheelbase" display style=[just=c vjust=t cellwidth=3.212cm tagattr='type:String format:Text'];
	  DEFINE mpg_city /"mpg_city" display style=[just=c vjust=t cellwidth=4.643cm tagattr='type:String format:Text'];
	  DEFINE EngineSize/"Engine Size" display style=[just=c vjust=t cellwidth=4.643cm tagattr='type:String format:Text'];
	  DEFINE Horsepower /"Horsepower" display style=[just=c vjust=t cellwidth=4.643cm tagattr='type:String format:Text'];

	  compute before origin/ style=[font=("Times New Roman", 11pt, bold ) background=grey ];
		line origin $varying80. ;
	  endcomp;

run;
ods excel close;
ods listing close;

Re: Report dataset to Excel in specific format.

andreas_lds — Wed, 24 Feb 2021 05:32:14 GMT

To move the origin above the table header replace "origin" by "_page_" in the compute statement:

compute before _page_ / ...;
  ...
endcomp;

I have no idea how to output "drivetrain" the way you want it.

Re: Report dataset to Excel in specific format.

Cynthia_sas — Wed, 24 Feb 2021 14:30:15 GMT

Hi:

I'm not sure I understand this report. Here are some first thoughts:

I thought that seeing your code might help, but that leads to more questions. Here are some other thoughts:

It doesn't look to me like the code you posted produces the picture you posted. So I am curious how you got the word Front into your posted picture and where that came from. Also curious about the variable "styl" and why both MAKE and TYPE have 2 usages and whether you want ORDER or DISPLAY for the usage.

Cynthia

Re: Report dataset to Excel in specific format.

SASuserlot — Wed, 24 Feb 2021 15:33:06 GMT

Thanks for you valuable suggestions Cynthia. the top pic, its the template they given in excel how they want the output of dataset. its not produced using the SAS. I requested how to produce that output, since I don't have much knowledge in producing excel especially this many horizontal headers. Some fellow member asked me just post code I used whatever I started so, I posted my sas code( I got some inputs from you because of that , Thanks). I believe my code not at all useful for the report I am looking to produce after reading your inputs. Varaible "Styl" I created because I am not sure how to bring "Drive Train" in the excel under style. Thanks for your inputs let me know if you have any questions.

Re: Report dataset to Excel in specific format.

Cynthia_sas — Wed, 24 Feb 2021 18:17:47 GMT

Hi:

I'm not going to get into any examples of ODS EXCEL. I always like to see what PROC REPORT gives by default before involving other destinations or trying to over control things like fonts and cellwidths and using options like PROTECTSPECIALCHARS which do not seem required for the output you describe. Also before I move heaven and earth to try to insert text strings into arbitrary places, or conform to possibly arbitrary cosmetic "requirements", I generally, like to make sure that people are aware of the formatting that can be done easily before jumping straight into formatting that seems arbitrary or unreasonable.

So, with extremely simplified code and taking the default HTML output, this is what I can get out of SASHELP.CARS without making any other variables in a DATA step:

As you can see, there are 2 possible options for the placement of the value for DRIVETRAIN, either in a COMPUTE BEFORE _PAGE_ or COMPUTE BEFORE DRIVETRAIN. The odd row arrangement you showed in your shot does not make sense to me. I did turn SPANROWS on in my code and stripped everything down in the code to the bare minimum. I am unable to force myself to use a style override without an area in parentheses, as you showed for your original DEFINE statements, so I removed most of them partly in the interests of simplification and partly because for testing purposes the over-abundance of overrides were not needed.

This program might not format or break as you want when you get more MAKEs, DRIVETRAINs or ORIGINs on the report. So if you envision more values for these variables on a final report or in a final Excel sheet, then I recommend sticking with the simplified program and get it working on your real data or with more groups.

Hope this helps,

Cynthia

Re: Report dataset to Excel in specific format.

SASuserlot — Wed, 24 Feb 2021 18:30:54 GMT

Thank you so much @Cynthia_sas . It helped me a lot. I will keep your suggestions in mind when I am trying to output something through ODS instead of doing complicated things. Thanks again