Exporting from SAS to Access

[wheddingsjr]

Good afternoon all

I am using SAS Enterprise 5.1 and I have been running queries out of it for a few months now and downloading the resulting data into an excel sheet using the following:

    PROC EXPORT DBMS = EXCELCS
    Data = Results
    FILE = 'C:\Results\SAS Results.xlsb'
    replace;
    PORT = 9621;
    SERVER = &_mymachine;
    RUN;

Unfortunately my last result set exceeds 3 mil lines and I cannot get it to export to excel even though I use the "Export As A Step In Project" and I am using excel 2016. With such a large dataset, can it be directly exported into Access?

Thanks in adavance

[collinelliot]

You could create a CSV or other delimited file, but then there's the question of what will open it.

[wheddingsjr]

Thanks Collinelliot, I will give that a try

[Kurt_Bremser]

Export to a common data transfer format like csv and read that into access. Using a textual format is the least painful way for such undertakings, IMO.

[wheddingsjr]

Thanks KurtBremser

That was the suggestion of another person as well. I will give that a shot and see what happens. The only other alternative is to break the year up into quarters or 2 month chunks for smaller data sets then merge them later (which I was desperately trying to avoid).

[rogerjdeangelis]

9 minutes for 2.9 million skinny three numeric variable dbload

I don't think there are any 'bulkload' options ot turning off logging for MS access

Too lazy to load char variables because I need to set lenghts?


data threemm;
 set sashelp.class;
 do i=1 to 150000;
   output;
 end;
run;quit;

libname mdb "d:/mdb/demo.accdb"  ;
data mdb.threemm ;
  set threemm(keep=age height weight);
run;quit;
libname mdb clear;


2096  libname mdb "d:/mdb/demo.accdb"  ;
NOTE: Libref MDB was successfully assigned as follows:
      Engine:        ACCESS
      Physical Name: d:/mdb/demo.accdb
2097  data mdb.threemm ;
2098    set threemm(keep=age height weight);
2099  run;

NOTE: There were 2850000 observations read from the data set WORK.THREEMM.
NOTE: The data set MDB.threemm has 2850000 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           9:21.06
      user cpu time       8:11.17
      system cpu time     1:08.43
      memory              2189.43k
      OS Memory           16628.00k
      Timestamp           03/31/2017 03:18:28 PM
      Step Count                        354  Switch Count  10


2099!     quit;
2100  libname mdb clear;
NOTE: Libref MDB has been deassigned.

[wheddingsjr]

whoaa...thats wayyy too technical for me to understand..lol...but I do know that I tried Collinelliot's and KurtBremsner's suggestion and tried to make a CVS. Unfortunately it was even too big for that. I think I may have no choice but to do them a few months at a time. Mind you I have over 3.5 mil lines and 132 columns. Its a HUGE set of data...but I appreciate all the help.

[Kurt_Bremser]

What do you plan to do with the data once you have it in the cheap toy software? In my mind, you need to do much more work in SAS so you have pre-analyzed data that fits into the maximum structures of office software.

[wheddingsjr]

KurtBresmer

Within the data are two subsets. The idea is to determine, based on certain fields, which lines appear in both subsets and single those lines out.

[Kurt_Bremser]

I'd be VERY surprised if that issue could not be solved here quicker than the import into Office.

[wheddingsjr]

I agree, but I am not all that versed in SAS (I just started using it a few months ago). I was going to use the code below but i have used it before and though it worked it wasnt as thorough as I had hoped. I ended up having to load the data into Access and found a ton of data that should have been included but was not for whatever reason.

PROC SQL;
CREATE Table results as
Select *
from step2
WHERE MEMNO
IN SELECT MEMNO
FROM RESULTS AS TEMP
GROUP BY MEMNO, BEGDATE
HAVING Count(*)>1
AND BEGDATE = RESULTS.BEGDATE
ORDER BY RESULTS.MEMNO, RESULTS.BEGDATE;
quit;

[Kurt_Bremser · Accepted solution]

First of all, I would never use the same table as input and output of a SQL step.

Second, I'd run the inner select for the where condition in a separate step to see if it yields a correct result in terms of the included MEMNO's.

As it is, the SQL you provided lacks brackets to correctly identify the extent/scope of the sub-select; this may cause your problems, as the conditions may be treated incorrectly by the SQL interpreter.

Try something like

proc sql;
create table lookup as
  select memno, begdate
  from results
  having count(*) > 1
  group by memno, begdate
;
create table endresult as
  select a.*
  from results as a, lookup as b
  where a.memno = b.memno and a.begdate = b.begdate
  order by a.memno, a.begdate
;
quit;

[wheddingsjr]

Thanks KurtBremser, I changed a few things in my statement to alter actual table names and incorrectly copied and pasted it in in my example, so you are right, I was not intending to use the same table as both input and output, my apologies. I did however try your suggestion and it worked for me. Thanks a lot, it really saved me a lot of work.