BookmarkSubscribeRSS Feed
pavan1
Obsidian | Level 7

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
8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

pavan1
Obsidian | Level 7

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Reeza
Super User

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.  

pavan1
Obsidian | Level 7

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.

pavan1
Obsidian | Level 7

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 ?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Reeza
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 913 views
  • 0 likes
  • 3 in conversation