Output to Variable Data Tables

[theponcer]

I am using SAS 9.4 and trying to break one large table up into an unspecified number of smaller tables. I know how to grab a distinct list of the Groceries I need:

proc sql noprint; 
select distinct Groceries
into :grocery_list separated by " "
from summary;
quit;

However, I am not sure how to use this to output each record to its corresponding Grocery. For example, if there are three distinct Groceries (Milk, Butter, and Cheese), I would like the Milk records to output to a separate Milk table, the Butter records to output to a separate Butter table, and so on.

I think the code should look something like this, but I am not sure how to get it to output to the correct table based on the value of the Grocieries field. 

data &grocery_list;
set summary;
output ?;
run;  

[ballardw]

---

@theponcer wrote:

I am using SAS 9.4 and trying to break one large table up into an unspecified number of smaller tables. I know how to grab a distinct list of the Groceries I need:

proc sql noprint; 
select distinct Groceries
into :grocery_list separated by " "
from summary;
quit;

However, I am not sure how to use this to output each record to its corresponding Grocery. For example, if there are three distinct Groceries (Milk, Butter, and Cheese), I would like the Milk records to output to a separate Milk table, the Butter records to output to a separate Butter table, and so on.

I think the code should look something like this, but I am not sure how to get it to output to the correct table based on the value of the Grocieries field. 

data &grocery_list;
set summary;
output ?;
run;  

---

What will you do with the separate data files? Generally for a vast majority of processing and manipulation it is better to leave the data in one set and use the values of a variable as either a BY variable or Class variable. Consider the following code that uses a data set you should have available:

proc means data=sashelp.class;
   class sex;
   var height weight;
run;

This produces a summary of other variables, height at weight , grouped by the values of the Sex variable. If I had wanted to do the same thing using your approach with two data sets I would have had to write to two Proc means statements. If I then actually wanted the results in a single table for a report I would then have to figure out a way to combine the two (or more tables) if my class variables had more levels.

Often is more efficient to do all of the processing with all of the records and then, if needed, use a reporting procedure to split things apart if needed.

[theponcer]

I used proc means/summary to create the dataset. I am trying to break the summary tables up so that I can export them to Excel for a pre-existing report. I need to get them out of SAS, which means making them smaller.

[Reeza]

You need individual Excel files? A single file with multiple tabs? Something else...

[theponcer]

A single file with multiple tabs. I was thinking I could break the summary dataset into smaller tables and export each table to the same file. If there is a better way I am definitely willing to learn.

[Tom]

---

@theponcer wrote:

A single file with multiple tabs. I was thinking I could break the summary dataset into smaller tables and export each table to the same file. If there is a better way I am definitely willing to learn.

---

Why not just use ODS EXCEL and PROC PRINT or PROC REPORT.

Use BY variable.  Tell ODS to make a new tab for each BY group.

[Reeza · Accepted solution]

*sort data for output;
proc sort data=sashelp.cars out=cars;
by origin;
run;

*turns off by titles;
options nobyline;

ods excel file='C:/_localdata/demo.xlsx' style=journal 
/*Set sheet interval to be by group, so each by group goes to a new sheet*/
options(sheet_interval='bygroup');

*print data to Excel;
proc print data=cars;
by origin;
run;

ods excel close;

*turns it back on;
options byline;

ODS EXCEL has a ton of options to customize your Excel file. 

---

@theponcer wrote:

A single file with multiple tabs. I was thinking I could break the summary dataset into smaller tables and export each table to the same file. If there is a better way I am definitely willing to learn.

---

[theponcer]

Sweet, thanks! I've heard of ODS Excel but never tried it before.

[theponcer]

This code is working according to the log, but when I try to open the Excel file it says that it found unreadable content, and then crashes. There are a lot of "." values in my table, so maybe it doesn't like that? I have also tried removing the bygroup option, but that doesn't seem to fix it either. 

I tried this code both with the sashelp table you provided and my table, and the same thing happened both times. Any ideas as to what is going wrong here? 

Is there an option in proc print that suppresses the display? I am wondering if the code is working, but I am accidentally creating a blank dataset because nothing is actually being printed. 

[Reeza]

Did you run the code exactly as shown?

What exact version of SAS do you have? You can check using the following code, the answer is in your log.

proc product_status;run;

I just tested it and it works fine using SAS 9.4 TS1M6. ODS EXCEL is only production as of 9.4 TS1M3 and really only stable as of M5 IMO. But, you can also use ODS TAGSETS.EXCELXP if you have an older version, you just need slight changes to your code. 

Or you could write a macro to export each sheet, but that's annoying when you have newer, easier options.

Show the exact code you're running if you're still having issues. 

[theponcer]

It looks like I have SAS 9.4M2. Does that mean I should be running using ods tagsets instead? Here is the log from what I am currently running. I was able to get the table to export after closing and re-opening a new instance of SAS, but it took three hours! Proc Export can do it in a few seconds. I think the issue is that I am printing the SAS results (using SAS EG) and then displaying them within the instance. Any ideas on why this is taking so long? Is there a way to not print the results within SAS and only print them to Excel?

22 GOPTIONS ACCESSIBLE;

23 *sort data for output;

24 proc sort data=shift.summary_sort out=summary;

NOTE: SGIO processing active for file SHIFT.SUMMARY_SORT.DATA.

NOTE: Data file SHIFT.SUMMARY_SORT.DATA is in a format that is native to another host, or the file encoding does not match the

session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce

performance.

25 by Product;

26 run;

NOTE: SGIO processing active for file WORK.SUMMARY.DATA.

NOTE: There were 432315 observations read from the data set SHIFT.SUMMARY_SORT.

NOTE: The data set WORK.SUMMARY has 432315 observations and 16 variables.

NOTE: Compressing data set WORK.SUMMARY decreased size by 31.96 percent.

Compressed is 1154 pages; un-compressed would require 1696 pages.

NOTE: PROCEDURE SORT used (Total process time):

real time 3.61 seconds

cpu time 2.79 seconds

 

27

28 *turns off by titles;

29 options nobyline;

30

31 ods excel file='L:\Data.xlsx' style=journal

32 /*Set sheet interval to be by group, so each by group goes to a new sheet*/

33 options(sheet_interval='bygroup');

WARNING: Unsupported device 'ACTIVEX' for EXCEL destination. Using device 'ACTXIMG'.

34

35 *print data to Excel;

36

37 proc print data=summary;

NOTE: SGIO processing active for file WORK.SUMMARY.DATA.

38 by product;

39 run;

 

NOTE: There were 432315 observations read from the data set WORK.SUMMARY.

NOTE: PROCEDURE PRINT used (Total process time):

real time 2:58:10.57

3 The SAS System 11:25 Wednesday, March 20, 2019

cpu time 1:48:37.10

[Reeza]

https://blogs.sas.com/content/sasdummy/2015/01/26/how-to-split-one-data-set-into-many/

I concur with others, this isn't a good way to handle data. 90% of cases I've seen this is not actually needed. 

The 10% where it is, most are usually required by client or regulatory body, or is for a procedure that doesn't support BY group processing but most do. 

---

@theponcer wrote:

I am using SAS 9.4 and trying to break one large table up into an unspecified number of smaller tables. I know how to grab a distinct list of the Groceries I need:

proc sql noprint; 
select distinct Groceries
into :grocery_list separated by " "
from summary;
quit;

However, I am not sure how to use this to output each record to its corresponding Grocery. For example, if there are three distinct Groceries (Milk, Butter, and Cheese), I would like the Milk records to output to a separate Milk table, the Butter records to output to a separate Butter table, and so on.

I think the code should look something like this, but I am not sure how to get it to output to the correct table based on the value of the Grocieries field. 

data &grocery_list;
set summary;
output ?;
run;  

---

[theponcer]

I am aware that it isn't the best way to handle data. I am trying to break the tables apart so that I can export them to Excel.