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
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;
You could create a CSV or other delimited file, but then there's the question of what will open it.
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.
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).
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.
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.
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.
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.
I'd be VERY surprised if that issue could not be solved here quicker than the import into Office.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.