topic Re: Import data from a file sas7bdat to excel in SAS Programming

Import data from a file sas7bdat to excel

makset — Fri, 22 Aug 2014 12:20:36 GMT

Hello

Is it possible to import the data into excel
File from sas7bdat

I ask because I make a lot of command export of sasa "proc export data = "
and each takes about half a second. Is there maybe a faster way.

Thank you for your help

Re: Import data from a file sas7bdat to excel

Kurt_Bremser — Fri, 22 Aug 2014 12:56:12 GMT

You cannot directly read .sas7bdat files in Excel. You either need the SAS Addin for MS Office, the SAS ODBC Driver (which needs a SAS/SHARE server) or a method to export your data into a format that Excel can read (which you obviously already have).

Re: Import data from a file sas7bdat to excel

jakarman — Fri, 22 Aug 2014 12:56:42 GMT

Looking for a sasviewer?

writing to a csv using a sas datastep?
Using ODS tagset ?
Using the libname excel / xlslx interface?

AMO (addin Microsoft office)?

Eguide connection?

There are so many ways, counting them and easily forgetting some option...

Re: Import data from a file sas7bdat to excel

makset — Sat, 23 Aug 2014 11:26:14 GMT

So can you tell me which is the fastest of all
and which is able to export to open an excel sheet (data based on these graphs)

Thank you for your help

Re: Import data from a file sas7bdat to excel

jakarman — Sat, 23 Aug 2014 12:02:44 GMT

They behave diiferent.
The sas-viewer has limited data-size but operates without sas-license.

You can export data to csv to get that into excel (windows only). When you get the data mailed this is a cheap quick way and fast to implement.
Every SAS-base environment is able to write data to a CSV.

Coded by hand it can be tuned to be run as mostly the fastest. After that you have to download that and import into Excel. The download being a challenge for performance, but focus on that.

ODS tagsets is more easy coding faster on that coding part when you have not only datasets but also reports.

Then libname excel/xlsx is supporting copy-past of datasets almost in a drag/drop approach. It is needing an environment supporting that (SAS/BASE Acsess pc-files)

When the server is running on a Unix you can implement the pc-fileserver on a Windows-server. There are more infrastructure requirments.

AMO (Addin for Microsoft Office) is the most elegant way. I transform MS-office as the user-interface for SAS.

This integration of MS-office and SAS. The requirements are licensing and a SAS server BI instaltation (running the SAS metadataserver)

Eguide is connecting to a SAS server BI installation supporting his own interface.

AMO and Eguide are hiding the communication (traffic for users)

Proc export and is taking about half a second.

Go for your real problem half a second is not an issue. Only when you this in the number of several thousands times (about 2hours) I can imagine it will become problematic.

That is rethinking on your process.

- Are they all necessary as push or can it be a pull. (batch processing) Is processing in parallel an option?

- Is it webbased, than you could try ods / datastep to get it all in split second. Gaining performance is in using sessions that are kept open and reused as shared resources.

- analytics? Normally not having strict time options.

Proc export is and easy coding way what also can be done in data-step when it is just exporting data.

Re: Import data from a file sas7bdat to excel

makset — Sat, 23 Aug 2014 19:30:07 GMT

I'll tell you how I'm doing now

I'm doing calculations in sas and using "proc expor" export data to excel file (call it "A" file) (excel files have from 10 to 140 KB) and then run the excel macro using dde, to refresh the link in the excel file (call it "B" files) which I have graphs based on these data. All operations are batch session.

My question is this
Is it possible Exporting data direct to a sheet in the workbook (file) "B"
not using (without) the file "A".

Requirements
- The excel file "B" must be always open
- Do not use dde because I do not know why but often stops working and I mus reboot the system

I have win7 and basic installation sas 9.2

Re: Import data from a file sas7bdat to excel

jakarman — Sun, 24 Aug 2014 08:02:41 GMT

Makset, with those specifications an answer better fit to your topic hidden in the question is possible.

Functional (tech):

* The functionality as described stepA and stepB would be nicely covered with AMO in interactive usage.
You do not have AMO and wanting batch processing.
* building graphs is highly improved with SAS. You could use Eguide for start of generating code.

With 9.3 and better with 9.4 if you do not have SAS/Graph licensed all kind of graphics is present with ODS graphics with the SAS base license.

You should have Eguide as using sas 9.2 on W7.

* DDE is originating as of the fist excel versions about 1993. Based on single machine spreadsheet processing and simulating the command as typed.
By this you see a lot reasons to avoid it. It could get hung fo several reasons

Solution architecture:

* you can try to run everything as SAS commands directly. I see no real requirement for this.
* What you could imagine is adding all data coming form sas to a dedicated named sheet of an existing spreadsheet (using a template?)

* After having added the data to a spreadsheet that one to be updated by an Excel macro
* All processing to be automated at windows using command (powerscript) processing when Excel neede to be updated. This code might be generated code by SAS processing.

Technical tools design:

* SAS (9.2) with xcmd setting open. If you use Eguide local, it is open again at windows with 9.3 it was closed at 9.1.3. It is Windows registry update to fix that.

* Automating Excel new topic searching and found

vba - Way to run Excel macros from command line or batch file? - Stack Overflow

Command-line switches for Excel - Excel http://support.microsoft.com/kb/291288/en (Microsoft) With /p <folder> /e (no popups) /t using a template, with the spreadsheet as default option

Running a macro when Excel starts - Excel (Microsoft -old) AUTO_open looks to be similar as autoexec of sas. Customize how Excel starts - Excel (holding shift to disable)

* Seeing proc export Base SAS(R) 9.2 Procedures Guide it is generating a CSV unless you have Access to pcfiles SAS/ACCESS(R) 9.3 Interface to PC Files: Reference.

In that case I would the xlsx approach. notice the libname statement usage. 42981 - Microsoft Excel files that have the .xlsx extension cannot be exported

* using the command processing think on adding a sleep/wait command as all excel spreadsheet processing will start almost simultaneously.

Using batch files (Microsoft)

Detail design:
* When needed create all wanted excels spreadsheets from a template. A SAS generated script with ms-commands on windows to execute.

* fill a predefined sheetname with data in all excel spreadsheets.

* Have those spreadsheet been updated by an autocall excel-macro in a script, A SAS generated script with ms-commands on windows to execute.

That Excel-macro should be smartly designed supporting several conditions (1/ copy 2/ open-generate graphs-close 3/ open-view graphs

Re: Import data from a file sas7bdat to excel

makset — Tue, 26 Aug 2014 14:16:15 GMT

hello

I have a question as to export to csv file

if sas export the column format datetime21. csv file
how to import it to excel in datetime format (import to me as text)

Thank you for your help

Re: Import data from a file sas7bdat to excel

RW9 — Tue, 26 Aug 2014 14:28:22 GMT

Hi,

Only just noticed this post, so maybe has already been covered. This type of question comes up a lot. Basically SAS will export its data as output to a specific format, this could be a doc, rtf, xls etc. This is output and does not invoke, nor change the destination. Whilst DDE (which is old tech) supported some functionality, it wouldn't be my goto choice. The way I see it is that you have an end result, that being an Excel file which you want to add in some data from another source. SAS cannot know or have functionality to output to another application in the exact way that someone not using SAS has created.

So look at it from the other side. You have an Excel file which someone has come up with, that is the principal item. Into that some data needs to be fed, irrespective of wether that is SAS or a database etc. I would thus propose that you have a base export from SAS to CSV or XLS, doesn't really matter, then create a macro using VBA which loads this data into your Excel file, processes it, re-creates pivot tables etc.

Re: Import data from a file sas7bdat to excel

Kurt_Bremser — Tue, 26 Aug 2014 14:34:52 GMT

Try to enter your timestamp manually in Excel, until you get it right. Then write a conversion step in SAS that produces exactly the same format, either with a predefined SAS format (there are LOTS of them) or by building a string variable step-by-step. Then import that via csv into Excel.

Re: Import data from a file sas7bdat to excel

jakarman — Tue, 26 Aug 2014 15:19:33 GMT

Excel is having a date-type SAS is having a date-type they are not equal needing a transformation.

SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition (Microsoft Excel Workbook Files) is having this documented for the libname access method.

Variables have defined some of the known dateformats are translated by the SAS interface (when correctly implemented).