topic Print column of charcter strings to different sheets in Excel in SAS Programming

Print column of charcter strings to different sheets in Excel

pavan1 — Mon, 06 Mar 2017 10:03:52 GMT

Hello,

Can anyone please help me with the following scenario?

I want to Output an excel workbook with different sheets, by Printing the Character String (to first cell of a Excel sheet) from column(or variable) of SAS Dataset, followed by data from the particular row (Ex: 10th row) in the sheet.

The Description column shown above, should be printed into respective sheets to Excel Workbook.

Ex: Rule_01 sheet should have "Description to print for Rule-1" text printed to the first cell of the sheet named Rule_01.

Thanks in Advance.

Pavan.ch

Re: Print column of charcter strings to different sheets in Excel

RW9 — Mon, 06 Mar 2017 10:31:01 GMT

Something like (and this is just example):

ods tagsets.excelxp file="c:\want.xml";

proc sort data=have (keep=rule) out=loop;
  by rule;
run;

data _null_;
  set loop;
  call execute('ods tagsets.excelxp options(sheet_name="'||strip(rule)||'";');
  call execute('proc print data=have; where rule="'||strip(rule)||'"; run;');
run;

ods tagsets.excelxp close;

You could of course do a similar thing with libname excel, proc export etc., the real trick is in getting the loop dataset and have the data _null_ generate the required code.

Re: Print column of charcter strings to different sheets in Excel

pavan1 — Mon, 06 Mar 2017 11:30:00 GMT

Hello,

Thanks for your reply.

Below is the code that i have used to make it more clear .

First of all, user will give the input data to Excel, for which they want the report to be generated. which is attached before (Rule, Description). By taking the input from rule column (Index sheet) . I have queried the data from PL/SQL and Exporting the data of respective Rules to different sheets of the output.XLS, which i could do it successfully.

Now what i want is, to print the Description of the respective rule in 1st cell of the particular Rule while Exporting the data to Excel.

Is there a way that i could acess the Description from the Rules dataset, so as to get the output?


proc import datafile='M:\SAS\Rules_Input.xlsx' 
out=work.example dbms=EXCEL replace ;
sheet="Index" ;
range="A1:B100";
getnames=YES;
run;

data rules;
set example;
if missing(rule) then delete;
run;


proc sql noprint;
	select distinct rule into: rule_list separated by ' ' from work.rules;
quit;


%macro test;
%local i next_rule ;
%let i=1;
%do i=1 %to %sysfunc(countw(&rule_list));
	%let next_rule=%scan(&rule_list,&i);
		proc sql;
		connect to oracle as odbc(&srvr readbuff=1000);
		create table &next_rule as select * from connection to ODBC
		(
			select * from &next_rule
		);
		DISCONNECT FROM ODBC;
		QUIT;
		RUN;

	proc export  data=&next_rule  dbms=EXCEL REPLACE 
    outfile="M:\SAS\Output.xls"; 
    sheet=&next_rule;
  	run;
	%end;
%end;
%mend test;

%test;

Re: Print column of charcter strings to different sheets in Excel

RW9 — Mon, 06 Mar 2017 11:37:27 GMT

The easiest whay is to put the rule description as a title, and in the options of the tagsets.excel use embed_titles.

ods tagsets.excelxp file="c:\want.xml" options(embedded_titles="yes");

proc sort data=have out=loop;
  by rule;
run;

data _null_;
  set loop;
  call execute('ods tagsets.excelxp options(sheet_name="'||strip(rule)||'";');
  call execute('title "'||strip(description)||'";');
  call execute('proc print data=have; where rule="'||strip(rule)||'"; run;');
run;

ods tagsets.excelxp close;

Re: Print column of charcter strings to different sheets in Excel

Reeza — Mon, 06 Mar 2017 12:52:29 GMT

Or a different structure where you create a Table of Contents on first sheet with hyperlinks to each sheet/rule.

TOC can be automated in TAGSETS.EXCELXP.

Re: Print column of charcter strings to different sheets in Excel

Reeza — Mon, 06 Mar 2017 12:54:15 GMT

Also, you can't go after the fact and export to a already created sheet/file in a specific cell. Are you on SAS 9.4? If do, using XLSX engine and RANGE could be an option. Or ODS Excel.

Re: Print column of charcter strings to different sheets in Excel

pavan1 — Mon, 06 Mar 2017 16:30:55 GMT

Hello Reeza,

thanks for your reply.

im using SAS 9.3 and jus want to export both data and Description/Titles for the respective rules at the same time by looping.

Re: Print column of charcter strings to different sheets in Excel

pavan1 — Tue, 07 Mar 2017 05:45:05 GMT

Hello RW9,

ods tagsets.excelxp file="c:\want.xml" options(embedded_titles="yes");

proc sort data=have out=loop;
  by rule;
run;

data _null_;
  set loop;
  call execute('ods tagsets.excelxp options(sheet_name="'||strip(rule)||'";');
  call execute('title "'||strip(description)||'";');
  call execute('proc print data=have; where rule="'||strip(rule)||'"; run;');
run;

ods tagsets.excelxp close;

Can you please give me the above example with proc export to excel ?

Re: Print column of charcter strings to different sheets in Excel

RW9 — Tue, 07 Mar 2017 09:14:10 GMT

Why do you want to use export, that has no formatting functionality at all, not sure you can embed titles or anything like that. Is it because you want an XLSX file (maybe use libname excel then if you have > 9.3). I specifically used tagset to get this additional functionality. To do it your way you will need to create a temporary dataset with no observations, and set labels as the data you want, then output that, not easy and I don't have time to mess around with such a thing. If you have to do this which is "Excel" thinking and not SAS/Databasing, then dump your data out to CSV and use VBA to process it into the form you want, far simpler.