About RW9

RW9 · ‎04-10-2014

Hi, I assume you have put: mixed=yes; in your proc import?

RW9 · ‎04-09-2014

Having utilized a macro found online and manipulating it to do much what you want, here is a VBA macro. So have a data\In nad data\out somewhere. Put your xlsb files in the in. then create a master excel file and and the macro into the code (alt-f11). Then alt-f8 run the code. It will ask you to locate the in folder, then open each file in that folder, saving the file as xlsx, then closing that file. You don't actually even need to save the master. Note this was just a quick manipulation to show operation not a full blown set of code (which should have error checking etc.). Sub GetFileNames() Dim xRow As Long Dim xDirect$, xFname$, InitialFoldr$ InitialFoldr$ = "C:\" ' With Application.FileDialog(msoFileDialogFolderPicker) .InitialFileName = Application.DefaultFilePath & "\" .Title = "Please select a folder to list Files from" .InitialFileName = InitialFoldr$ .Show If .SelectedItems.Count <> 0 Then xDirect$ = .SelectedItems(1) & "\" xFname$ = Dir(xDirect$, 7) Do While xFname$ <> "" Set wb = Workbooks.Open("s:\temp\rob\data\in\" & xFname$) wb.SaveAs "s:\temp\rob\data\out\" & Replace(xFname$, ".xlsb", ".xlsx"), FileFormat:=51 wb.Close False xRow = xRow + 1 xFname$ = Dir Loop End If End With End Sub

RW9 · ‎04-09-2014

Yep, would agree with KurtBremser there, avoid Excel for any vital data transfer. However you don't need to modify the original files. In my example of the Excel Macro what I would do is to setup a folder called Data\In, and a folder Data\Out. Put your original files in the in area. then write a master Excel file with one macro which opens each file in \In, then calls the save as Excel function to save the file to the \Out folder. Hence you would have two versions, the original and the new one. Then use the new ones to read into SAS.

RW9 · ‎04-09-2014

Did you try the libname approach? Two other things I can think of unless someone has the mixed working with.xlsb files: -> Have a master Excel file with a VBA macro in. That macro could open each file, say in a selected folder, and save it out as .xlsx. Then you would be able to use the above examples. -> Read the data directly from the underlying xml. This is a difficult path however, unzip the file, then parse the xml in the subfolders.

RW9 · ‎04-09-2014

Strange, do you have SAS/ACCESS component? It should be available in SAS 9.2 if you have SAS/ACCESS: SAS/ACCESS(R) 9.2 Interface to PC Files: Reference, Second Edition

RW9 · ‎04-09-2014

Missing the engine statement - excel, please see my previous post.

RW9 · ‎04-09-2014

Oh, it works on xlsx, how annoying. Have you tried libnaming to the file: libname my_xls excel "S:\Temp\Rob\tmp.xlsx" mixed=yes; then: data test; set my_xls.'sheet1$'n; run; See what that does.

RW9 · ‎04-09-2014

Hi, Use: libname my_xls excel "S:\Temp\Rob\tmp.xlsx" mixed=yes; This will show you all the worksheets within your XLS file, it should be a simple matter to loop over them and create datasets. E.g. data sheet1; set my_xls.'sheet1$'; run; Reference: http://www2.sas.com/proceedings/sugi31/024-31.pdf

RW9 · ‎04-09-2014

Sorry, just to add as I don't feel I answered your question, the ods csv is a tagset, which you can find by right clicking on Results in Results window and selecting templates. Under one of the folders you will find Tagsets and in there one called csv. If you double click to open the code you will see all the options you can use with that tagset. I think the one you are looking for is: quoted_columns: ... putlog " Will be deprecated in a future release when it is no long er needed."; putlog " "; putlog "Quoted_columns: Default Value ''"; putlog " A list of column numbers that indicate which values shoul d be quoted"; putlog " ie. Quoted_columns=""123"""; putlog " "; putlog "Bylines: Default Value: No"; putlog " If yes bylines will be printed"; putlog " ";

RW9 · ‎04-09-2014

Hi, One way: data _null_; set sashelp.cars (keep=make model type); file 's:\temp\rob\temp.csv'; put '"' make '","' model '","' type '"'; run ;

RW9 · ‎04-09-2014

Hi, Sorry, your test data doesn't match the logic given. Here is a short code to what you require, however there are different results, for instance 2000 + -50 = 1950 not 2050 given in your test data. data want; infile datalines delimiter=','; input acct amt1 amt2 want_variable; datalines; 123,100,2000,2000 123,-50,2000,2100 123,20,2000,2050 123,30,2000,2070 123,40,2000,2100 123,-10,2000,2140 234,30,1500,1500 234,-30,1500,1530 234,200,1500,1500 234,-80,1500,1700 234,20,1500,1620 234,10,1500,1640 ; run; data inter; set want; id=_n_; run; proc sql; create table INTER2 as select BASE.*, PREV.AMT1 as PREV_AMT1, PREV.AMT2 as PREV_AMT2, case when PREV.AMT1 is null and PREV.AMT2 is null then BASE.AMT2 else PREV.AMT1 + PREV.AMT2 end as WANT_CALCULATED from WORK.INTER BASE left join WORK.INTER PREV on BASE.ACCT=PREV.ACCT and BASE.ID=PREV.ID+1; quit;

RW9 · ‎04-09-2014

Hi, Try adding mixed=yes; after sheet line. However you may find that your system is setup to only check a few records, so you may need to update guessingrows in your registry to a higher numer (i.e. number of rows it checks to see type of format). Alternatively save the xlsb as csv and read it in manually using infile where you can setup explicit formats/lengths etc.

RW9 · ‎04-09-2014

Hi, Just to add another method avoiding macros and macro variables: data want; set have; attrib calc_date format=$20.; run; data _null_; set have; call execute('proc sql; update WORK.WANT set CALC_DATE=(select DATE from WORK.'||strip(dataset)||' where NAME="'||strip(name)||'") where NAME="'||strip(name)||'"; quit;'); run;

RW9 · ‎04-09-2014

Hi, For data on separate sheets: ods tagsets.excelxp file="c:\output.xls" style=statistical options (frozen_headers="1" absolute_column_width="none" zoom="80"); proc sql; create table TMP as select distinct MEMNAME from DICTIONARY.TABLES where LIBNAME="***insert your library name here***"; quit; data _null_; set tmp; call execute('ods tagsets.excelxp options (sheet_name="'||strip(memname)||'"); proc report data=***insert your libname here***.'||strip(memname)||'; run;'); run; ods tagsets.excelxp close; If you need it in one sheet then drop the options(sheet_name part.

RW9 · ‎04-09-2014

Hi, Something like (and this is guessing without example data): proc sql; create table WANT as select CEO.CEO, CEO.GENDER, MRET.AVG_MRET3 from (select distinct CEO,GENDER from CEO where GENDER="Female") CEO left join (select distinct CEO, AVG(MRET3) as AVG_MRET3 from MRET3 group by CEO, YEAR) MRET on CEO.CEO=MRET.CEO; quit;

Re: How to import excel with guessingrow function

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

Re: Deleting rows in Excel with SAS

Re: PROC SGPLOT one command to produce graph for each case? And, quest...

Re: Deleting rows in Excel with SAS

Re: put statement and line break using /

Re: SAS code

Re: How to read from pdf to SAS

Re: Removing last word with length 1 or 2

Re: How to read from pdf to SAS

Re: How to read a macro variable value and convert it in SAS data set

Re: proc format in permanent library

Re: Copy all tables beginning with certain keyword from one lib to oth...

Re: Automated jobs failing because SAS logs contains Warning messages.

Re: Error: BY variables are not properly sorted on data set

Re: Difference between itt and safety populations

Re: ods excel -multiple sheets

Re: Lag function help

Re: ERROR: User does not have appropriate authorization level for file...

Re: Multiple graph in one page

Maxims of Maximally Efficient SAS Programmers

Re: Proc Import DBMS=CSV, incorrectly assigning informats mmddyy10. in...

Re: PROC IMPORT Excel file .xlsb by using SAS 9.3

Re: PROC IMPORT Excel file .xlsb by using SAS 9.3

Re: PROC IMPORT Excel file .xlsb by using SAS 9.3

Re: excel to dataset

Re: excel to dataset

Re: PROC IMPORT Excel file .xlsb by using SAS 9.3

Re: excel to dataset

Re: unexpected result using PROC PRINT with ODS CSV

Re: unexpected result using PROC PRINT with ODS CSV

Re: cumulative sum

Re: PROC IMPORT Excel file .xlsb by using SAS 9.3

Re: proc sql loop

Re: Querying Multiple Datasets Into One Excel File

Re: SQL help in SAS, please!

SAS Global Forum 2018