Selecting Columns and Exporting

[deleted_user]

Hi,
This is really driving me mad. I have large SAS data set which I need
to write to excel, unfortunately, I can't work out (or my version of SAS doesn't have the ability) to write to a single .xlsx file. So, I would like to split the dataset up into roughly x4 smaller tables. The variables look like:

DATE stock1000 stock10010 stock10015 ... etc

where everything after DATE is always indexed logically as stockX but not
necessarily in numerical order. Simply, how can I check the width of the table and select the first, say, 200 variables, then move on to the next 2000 variables etc until the end. I'm sure this should be easy but I really cannot work it out.

Many thanks.

[SPR]

Hello Thepowertoknow?,

This is a possible solution:
[pre]
data i;
stock1=10; stock5=20; stock2=30; stock10=40;
run;
%let ds=3;
proc SQL;
select COUNT(distinct name) as ns into :ns
from SASHELP.vcolumn
where LIBNAME="WORK" and MEMNAME="I";
%let ns=%TRIM(&ns);
select name as name into :n1-:n&ns
from SASHELP.vcolumn
where LIBNAME="WORK" and MEMNAME="I";
quit;
%macro a;
%local i is ie;
%do i=1 %to &ns %by &ds;
%let is=&i;
%let ie=%EVAL(&is+&ds-1);
%if &ie > &ns %then %let ie=&ns;
data r&is;
set i;
keep &&n&is--&&n&ie;
run;
%end;
%mend a;
%a
[/pre]
The only thing you have to change (except library and dataset names) is %let ds=200;
Sincerely,
SPR

[Ksharp]

I think SAS have the ability to write to a single .xlsx file.

[pre]



[/pre]


proc report data=temp
nowd out=want1(drop=_break_);

 column date -- stock10020; /* ' -- ' define a range of variable in your dataset

                            
which you can split dataset into several
sub-dataset  */

 define date /display;      * force dataset to be detial report;

run;

Ksharp

[Cynthia_sas]

Hi:
As it states in this note:
http://support.sas.com/kb/32/455.html

The EXCEL and XLS engines do not support importing and exporting of Microsoft Office 2007 files. These files have an extension of .xlsx, which is based on the Open XML standard. This type of file also allows for 16,384 columns and 1,048,576 rows.

Support for Office 2007 files is available in SAS® 9.2 Phase 2 (TS2M0). Contact SAS Customer Service, your account team, or SAS Technical Support to request the Phase 2 software.

SAS Representatives at a company site can request the software from the link listed in SAS Note 35445


The original post did not say what version of SAS was being used. If SAS 9.2 Phase 2 is being used, with either the LIBNAME Excel engine or PROC EXPORT to export to a proprietary .XLSX file, then it would be a SAS dataset (not ODS output) that would be exported from SAS to Excel.

To the extent that PROC REPORT can create an output dataset, then the dataset created by PROC REPORT could be exported to an XLSX file format. This is the newest Microsoft XML format file.

The older Microsoft XML format (Spreadsheet Markup Language XML) from Office 2003 is the kind of XML output created with ODS TAGSETS.EXCELXP. Generally people use TAGSETS.EXCELXP when they want their output REPORTS (not their DATASETS) in Excel -and- they want the styles, colors and fonts from SAS to carry over to Excel. There is a difference between Office 2003 XML (.xml) and Office 2007 XML (.xlsx).

Depending on what the original poster's constraints are, they may or may not be able to use PROC REPORT effectively to create output for Excel.

cynthia

[deleted_user]

Hi

Thanks for the reply. FYI - I am using SAS 9.1.3.

My solution so far has been to use ... http://support.sas.com/kb/36/904.html ...
in order to write the columns of the data across multiple sheets (90 of them) each
with 255 (ish) columns. I then have a VBA programme that writes these sheets to
individual manageable excel files. For some reason, I couldn't get the programme
to work with .xlsx files but it does do.xls files fine.

However, I still have a problem in that I need to select a specific set of columns
since I need to repeat this process for the same columns with many more
observations.

Thanks for the code SPR but I can't get it to run. I simply changed the library and
member name and choose ds = 200 to select 200 columns at a time:

LIBNAME MyLib 'C:\data_monthly';

data i;
stock1=10; stock5=20; stock2=30; stock10=40;
run;
%let ds=3;
proc SQL;
select COUNT(distinct name) as ns into :ns
from SASHELP.vcolumn
where LIBNAME=MyLib and MEMNAME=stocks_sorted;
%let ns=%TRIM(&ns);
select name as name into :n1-:n&ns
from SASHELP.vcolumn
where LIBNAME=MyLib and MEMNAME=stocks_sorted;
quit;
%macro a;
%local i is ie;
%do i=1 %to &ns %by &ds;
%let is=&i;
%let ie=%EVAL(&is+&ds-1);
%if &ie > &ns %then %let ie=&ns;
data r&is;
set i;
keep &&n&is--&&n&ie;
run;
%end;
%mend a;
%a

Thanks in advance - this will be very useful.

[SPR]

What errors did you get?

I see at least 2 obvious errors:

instead of
where LIBNAME=MyLib and MEMNAME=stocks_sorted;
should be
where LIBNAME="MYLIB" and MEMNAME="STOCKS_SORTED";


SPR

[deleted_user]

Hi, yes - I got that. I get

WARNING: INTO Clause :n1 thru :n0 does not specify a valid sequence of macro
variables.

I'm new to SAS, I have just come from Matlab / Gauss, but i'm keen to learn 🙂 (I can see how useful it will be). Maybe it would help if I understood the programme better. PROC SQL counts the number of columns in the data set. The variable 'ds' controls the number of columns that are selected by the macro.

You also initiate a dataset I which has 4 variables each with an observation. How do I use this to select and create separate data sets each with ds = 200 columns so that the entire original table is split up into smaller chunks.

Thanks again.

[SPR]

Does your code work or not?

One more error that I see in your code is that instead of

set i;

should be

set mylib.stocks_selected;

As to your question.

"You also initiate a dataset I which has 4 variables each with an observation. How do I use this to select and create separate data sets each with ds = 200 columns so that the entire original table is split up into smaller chunks."

I've created this dataset because I do not have a sample of your data. Instead of it you have your own dataset mylib.stocks_selected. I recommend to take the SAS Macro course to better understand this program.

Sincerely,
SPR

[deleted_user]

Hi, I'll be doing plenty of tutorials in the coming days.

I think the error isn't from the macro though ... the SQL statement :

proc SQL;
select COUNT(distinct name) as ns into :ns
from SASHELP.vcolumn
where libname = 'C:\data_monthly'
and MEMNAME="stocks_sorted";
%let ns=%TRIM(&ns);
select name as name into :n1-:n&ns
from SASHELP.vcolumn
where libname = 'C:\data_monthly'
and MEMNAME="stocks_sorted";
quit;


isn't selecting any rows. I think I understand the code but I don't get why we need the TRIM function ? In any case, the log is giving me:

WARNING: INTO Clause :n1 thru :n0 does not specify a valid sequence of macro
variables.
NOTE: No rows were selected.


Thanks

[SPR]

Hello,

'C:\data_monthly' is not a library reference that is required. This is the corrected version of your code.

This line should be at the beginning of the program:
[pre]
libname mylib "C:\data_monthly";
[/pre]

Pay attention on capital letters in MYLIB and STOCKS_SORTED - it is important.
[pre]
proc SQL;
select COUNT(distinct name) as ns into :ns
from SASHELP.vcolumn
where libname = "MYLIB"
and MEMNAME="STOCKS_SORTED";
%let ns=%TRIM(&ns);
select name as name into :n1-:n&ns
from SASHELP.vcolumn
where libname = "MYLIB"
and MEMNAME="STOCKS_SORTED";
quit;
[/pre]

"I think I understand the code but I don't get why we need the TRIM function ?" Suppose ns=5 then without %TRIM ns will look like ns= 5 (several blanks and then 5). It means that n&ns translates into n 5 instead of n5. %TRIM deletes these leading blanks.

SPR

[deleted_user]

Hi,

Thanks a lot for your help SPR. Code works a treat and is very useful. I learnt something too !

[Peter_C]

> "I think I understand the code but I don't get why we
> need the TRIM function ?" Suppose ns=5 then without
> %TRIM ns will look like ns= 5 (several blanks and
> then 5). It means that n&ns translates into n 5
> instead of n5. %TRIM deletes these leading blanks.
>
without the %TRIM(), so does
%let ns= &ns ;

( or am I mistaken?)

[SPR]

Hello Peter.C,

You are absolutely right. However, I prefer %TRIM because it is more intuitive.

Sincerely,
SPR