How to View Remote Server Files on Your PC

Many of us create and store files on remote servers. One way of viewing the files you create is to use Proc Download to export them to your local libraries, but downloading large files can be time-consuming. Fortunately, there is an easier way.

First, I connect to the server and rsubmit my libname statement with the path to its location on the server; then I submit a second libname statement locally using the same libref. After submitting the following code, I can open Explorer, navigate to Libraries, and the library I specified on the remote server is now available for me to browse.

rsubmit;
libname WHP “/projects/hedis/hedis/whp”;
endrsubmit;
/*submit locally*/
libname WHP server=remoteservername; /*view remote server files on local machine*/

In-Database Processing with SAS 9.2

There are some new SAS In-Database processing features available with version 9.2. In addition to the explicit pass-through syntax with Proc SQL option, SAS has expanded its capacity to generate DBMS-specific or “native” SQL for Proc SQL and even non-SQL procedures to do more processing within the native databases and minimize I/O. Seven non-SQL procedures are now supported in Oracle and DB2, including Proc Freq, Proc Means, Proc Summary, Proc Tabulate, Proc Report, Proc Rank, and Proc Sort (additional procs are supported in Teradata). System options to enable additional output to the log are SASTRACE (see previous post) and SASTRACELOC. Use system options DIRECT_SQL and SQLGENERATION to enable and disable in-database processing and try comparing log output with and without in-database processing.

BASUG Meeting Notes: September 2012

I attended the third quarter BASUG (Boston Area SAS User Group) meeting in Cambridge, MA at the Microsoft NERD center on September 20th. Morning speakers included Craig Dickstein of Tamarack Professional Services and Paul Gorrell of IMPAQ International. Craig Dickstein is one of the authors of Health Care Data and SAS and has worked with Cigna as a HEDIS code reviewer. An afternoon training on using SAS to analyze publicly available healthcare data sets was also led by Paul Gorrell.

The entire day focused on healthcare data, with the following presentations: “Data Hygiene Routines for Administrative Healthcare Data”, “Calculating the Hospital Readmission Interval”, and “Using SAS to Generate Estimates of U.S. Prescription Drug Cost and Use”. Dickstein’s presentation on “data hygiene routines” included a useful overview of the architecture of ICD-9 diagnosis codes, CPT codes (categories I-III), and HCPCS procedure codes. His code samples demonstrated how to create procedure code lookup tables with Proc Format and use these lookup tables to identify bad values. His second presentation described the challenges of calculating re-admission intervals and presented some alternatives to using the LAG function, including a detailed discussion of how the Program Data Vector (PDV) works in SAS. Finally, Paul Gorrell discussed how to replicate the numbers found in commonly cited statistics such as “5% of Americans make up 50% of U.S. health care spending” by using SAS/STAT survey procedures such as Proc Surveyfreq and Proc Surveymeans in combination with the HRQ data files available from the Medical Expenditure Panel Survey (MEPS). The next quarterly meeting is scheduled for December 11th, 2012.

UConn SAS Day

I attended a UConn SAS Day last week at the UConn School of Business Graduate Business Learning Center in Hartford, CT. Ram Gopal, department head of Operations and Information Management at UConn, gave welcoming remarks highlighting UConn’s new MS in Business Analytics and Project Management program that is now available at the Hartford campus. The first presenter, Pete Bothwell, Senior VP of Enterprise BI & Analytics at Travelers, focused on analytics in the property and casualty environment. The second presenter, Jon Sall, co-founder and Executive Vice President of SAS, demonstrated the use of JMP, a SAS tool used for graphic data analysis which can be used to bundle large amounts of data into meaningful statistical graphics. He used it to showcase processing times for data sets of various sizes and shapes, ending with an impressive graphical display of census data over time cycling through thousands of variables. Finally, the last presentation delivered by Radhika Kulharni, VP R&D Analytics at SAS, complemented John Sall’s topic on how SAS can be used to process large amounts of data in seconds. She focused on the use of distributed computing environments such as SAS GRID and products such as SAS Scoring Accelerator and SAS Analytics Accelerator which are used to process data inside the database itself, minimizing I/O and processing time. She then discussed the use of applying these analytic capabilites to customer behavior: for example, tailoring a coupon for a particular customer.

To view more on SAS Visual Analytics tool:
http://www.sas.com/technologies/bi/visual-analytics.html

For more about JMP:
www.jmp.com

Creating Row Numbers with Proc SQL

Some of you may be familiar with the Proc SQL NUMBER option. This option displays row/observation numbers with a report; however, it does not store these row numbers in a dataset. In order to create and store these row numbers, use the MONOTONIC() function. Here’s an admissions-related example of using MONOTONIC() with PROC RANK to create a list of unique procedure codes ranked by procedure type for each case id:

/*create listing of unique procedure codes*/
proc sql;
create table unique_procs as
select monotonic() as row,
sq.* from
(select
case_id,
mbr_num,
svc_dt,
proc_cd,
midsu_proc_mdfr_cd,
proc_ty,
case when proc_ty IN(‘4′,’5′,’RV’)then proc_cd end as rev_proc,
case when proc_ty in (‘7′,’HC’) then proc_cd end as hcpcs_proc,
case when proc_ty IN(‘1′,’CP’) then proc_cd end as cpt_proc,
case when proc_ty IN(‘9′,’IC’) then proc_cd end as icd9_proc
from work.ppo_ip_claim_all_&mkt
group by
case_id,
mbr_num,
svc_dt,
proc_cd,
midsu_proc_mdfr_cd,
proc_ty
)sq
order by
case_id,
proc_ty
;
quit;

/*number fields*/
proc rank data=work.unique_procs out=work.unique_procs_ranked ties=low;
by case_id proc_ty;
var row;
ranks ln_num;
run;