Direct access to Dataset records – SAS Point option ( point= )
Normally data records are read sequentially from the source data set. However, you can modify the read order using direct access. To do this you should use the point= option in the set statement.
Data new_ds;
set old_ds point=5;
stop;
run;
This example would result in a single record in the new_ds data set. Notice the use of the STOP command. This is generally good to use in the event that your statement creates a continuous loop.
This option can be used for many things and can make your code more efficient.
An example of more efficient code:
Data new_ds;
Set old_ds;
if _N_=5000;
run;
If your goal in the code above was to grab the 5000th record, you would be reading the full data set and processing the IF statement against them.
Data new_ds;
Set old_ds point=5000;
stop;
run;
The code above only traverses the data set a single time for record 5000. Thus is much more efficient for large data sets.
You could also incorporate the point option for processing every N record. If you use the point option for something else, leave us a comment with your example.
SAS fastload to Teradata
NOTE - SAS changed the default settings for fastload in SAS9. This has not been an improvement. SAS has documented how to change the setting back to the previous default in their note here... click here
The fastload facility for Teradata through SAS ACCESS is one that every analyst loading data into Teradata should be aware of. This tool allows you to pump data from SAS to Teradata in the quickest manner. The target table must be empty. There are other limitations as well, but most are uncommon problems.
To use the fastload option, follow the example below (SAS9 and above may require more manual settings to see max performance):
data td_lib.empty_table (bulkload=YES);
set source_table;
run;
Another way...
proc append data=source_table
base=td_lib.empty_table
(bulkload=YES bl_log=append_err_output);
run;
If your organization uses Teradata and could use some help utilizing the full power of SAS with Teradata, we are available to assist you.
SAS Proc Contents… What is that data?
With the release of EG (Enterprise Guide) and analysts becoming more comfortable with a point and click environment, it's not as common that we use Proc Contents. This is another one of those tools that make up the basics in SAS. Yes, you can right click on a dataset in EG and see the 'properties' to get most of this data.
So here is an example:
PROC CONTENTS DATA=sashelp.dplog;
RUN;
In the output, you should first expect to see all of the dataset properties. Things like name, type, creation and modification dates, etc. Next is the engine information. This tells us how SAS and the dataset file are setup. Last we get the variables and attributes. You should see things like variable names, type, length, formats and labels (which can be very helpful and not overlooked in permanent datasets).
Get stock quotes into SAS (macro)
Here is a great one for you. I've been doing this through a convoluted process involving SAS and PHP for a while now.
Recently I ran across a colleage who had a question regarding downloading stock data into SAS on demand. I explained my process and that I would be happy to help. Before sending over my code, I decided to do some research to make sure there was no one doing a better job.
Low and behold, there was. I ran across this macro developed byRichard A. DeVenezia. He posted his macro here.
Here is a copy of his macro:
%macro quotes (symbol=, start=, end=, prompt=NO);
%* Richard A. DeVenezia, 4/9/00
%* Quotes: A macro to retrieve historical daily stock quotes from Yahoo;
%*
%* symbol - ticker symbol
%* start - starting date mm/dd/yy
%* end - ending date mm/dd/yy
%* prompt - if YES then raise a macro window, otherwise proceed
%*
%* Yahoo url that delivers a CSV of daily stock quotes looks like
%* http://table.finance.yahoo.com
%* /table.csv?s=<SYMBOL>
%* &a=<FROM_MONTH>
%* &b=<FROM_DAY>
%* &c=<FROM_YEAR>
%* &d=<TO_MONTH>
%* &e=<TO_DAY>
%* &f=<TO_YEAR>
%* &g=d
%*
%* MONTH 0 is January
%*
%* 12/08/03 rad Thanks to Andrew Farrer for v.8 bugfix (%window)
%* 4/20/07 rad Use IS8601DA to read date value
%*;%local symbol start end;
%local s _s _e a b c d e f g url;%if (%superq(symbol) eq ) %then %let symbol=msft;
%let symbol = %upcase (&symbol);
%if (%superq(end) eq )
%then %let end = %sysfunc(today(),mmddyy8);%if (%superq(start) eq ) %then %do;
%let start = %sysfunc(inputn(&end,mmddyy8.));
%let start = %eval (&start-30);
%let start = %sysfunc(putn(&start,mmddyy8.));
%end;%if (%upcase(&prompt) eq YES) %then %do;
%window Quotes rows=12 columns=30
#2@2 "Symbol: " symbol 4 c=blue a=rev_video
#4@2 "Start: " start 8 c=blue a=rev_video
#6@2 "End: " end 8 c=blue a=rev_video
;%display Quotes;
%end;%let symbol = %upcase (&symbol);
%if (%superq(end) eq )
%then %let _e = %sysfunc(today());
%else %let _e = %sysfunc(inputn(&end ,mmddyy10.));%if (%superq(start) eq )
%then %let _s = %eval (&_e-30);
%else %let _s = %sysfunc (inputn(&start,mmddyy10.));%let s = &symbol;
%let a = %sysfunc (month(&_s)); %let a=%eval(&a-1);
%let b = %sysfunc (day (&_s));
%let c = %sysfunc (year (&_s));
%let d = %sysfunc (month(&_e)); %let d=%eval(&d-1);
%let e = %sysfunc (day (&_e));
%let f = %sysfunc (year (&_e));
%let g = d;%let and = %str(&);
/*
%let url = http://chart.yahoo.com:80/table.csv?s=&s;
%let url = &url.&and.a=&a.&and.b=&b.&and.c=&c;
%let url = &url.&and.d=&d.&and.e=&e.&and.f=&f;
%let url = &url.&and.g=&g.&and.q=&q.&and.y=&y.&and.z=&z;
*/%let url = &url.http://table.finance.yahoo.com:80/table.csv;
%let url = &url.?s=&s;
%let url = &url.&and.a=&a.&and.b=&b.&and.c=&c;
%let url = &url.&and.d=&d.&and.e=&e.&and.f=&f;
%let url = &url.&and.g=&g;%put &url;
filename quotes URL "&url";
data &symbol;
infile quotes dlm=',';retain symbol "&symbol";
if _n_ = 1 then input; * skip header row;
* input Date date9. Open High Low Close Volume;
input Date is8601da. Open High Low Close Volume;format Date mmddyy10.;
format Volume comma11.;
run;%bye:
%mend;
EXAMPLE
%quotes(symbol=mot, start=01/01/2005);
Beautifully done and many thanks to you Mr. DeVenezia!
SAS Proc Import
First, there is no way I can include everything that you can do with proc import in one post. So let me cover a couple of simple examples. Let me begin by saying that Proc Import does exactly what it sounds like, it imports data into SAS. The availability of given options within Proc Import are solely based on the version of SAS and the packages/add-ons your have for it.
The most basic version of proc import is as follows:
proc import
out=new_dataset
file="some file in some directory (c:/this_directory/file.csv)"
dbms=DLM REPLACE
delimiter=',' ;
run;
Now, "out" signifies the name and location of the new dataset. "file" is the location and name of the incoming data. "dbms" is the type of import you are doing.
| Identifier | Input Data Source | Extension |
|---|---|---|
| ACCESS | Microsoft Access database | .MDB |
| DBF | dBASE file | .DBF |
| WK1 | Lotus 1 spreadsheet | .WK1 |
| WK3 | Lotus 3 spreadsheet | .WK3 |
| WK4 | Lotus 4 spreadsheet | .WK4 |
| EXCEL | Excel Version 4 or 5 spreadsheet | .XLS |
| EXCEL4 | Excel Version 4 spreadsheet | .XLS |
| EXCEL5 | Excel Version 5 spreadsheet | .XLS |
| EXCEL97 | Excel 97 spreadsheet | .XLS |
| DLM | delimited file (default delimiter is a blank) | .* |
| CSV | delimited file (comma-separated values) | .CSV |
| TAB | delimited file (tab-delimited values) | .TXT |
The "delimiter" is specific to the DLM type. It tells SAS what the delimiter is within the incoming data.
"REPLACE" tells SAS to replace the dataset if there is already one named the same as specified by the "out" statement.
This is a basic use of Proc Import. It is a powerful tool with many uses and options. We will expand on this in future posts.