Hi guys, Many thanks for your help. Unfortunately the code still doesn't seem to work with Excel 2003 - so I shall contact Tech Support later on. Thanks again, Katie ... View more

thank you for helping me. In regards to the libname, would I have to write that everytime I open sas. Also how can i figure out why it has become read-only? ... View more

Thanks. Will try it out. Thanks for all the help! Shivek Message was edited by: Shivek ... View more

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

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 ... View more

Thanks Curtis, that worked! Jeff ... View more

Curtis, Thanks for your reply. When I run your code I get the following errors: 371 put &q.[select("R&&Header&I..C1:R&&Header&I..C1")]&q.; put ______ 68 371 ! '[font.properties("MS Sans Serif","Bold",10,,,,,,,1)]'; ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, arrayname, #, (, +, /, //, ;, @, @@, OVERPRINT, _ALL_, _BLANKPAGE_, _ODS_, _PAGE_. ERROR 200-322: The symbol is not recognized and will be ignored. ERROR 68-185: The function SELECT is unknown, or cannot be accessed. ERROR: Undeclared array referenced: NAME. _ 22 200 ... View more

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. ... View more

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. ... View more