DATA Step, Macro, Functions and more

Print column of charcter strings to different sheets in Excel

Reply
Occasional Contributor
Posts: 8

Print column of charcter strings to different sheets in Excel

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.

 

Description to Sheets.PNG

 

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


Description to Sheets.PNG
Super User
Super User
Posts: 7,401

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

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.

Occasional Contributor
Posts: 8

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

[ Edited ]

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;

Super User
Super User
Posts: 7,401

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

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;
Super User
Posts: 17,818

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

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.  

Occasional Contributor
Posts: 8

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

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.

Occasional Contributor
Posts: 8

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

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 ?

Super User
Super User
Posts: 7,401

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

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.

Super User
Posts: 17,818

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

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. 

Ask a Question
Discussion stats
  • 8 replies
  • 166 views
  • 0 likes
  • 3 in conversation