Chris Hemedinger

12月 132017
 

I'm a big fan of the Import Data task in SAS Enterprise Guide, especially for its support of text-based files (CSV, tab delimited, fixed width, and more). There's no faster method for generating SAS code that reads your data exactly the way you need it. I use the tool so often that I take for granted some of its neatest features, and I forget that many new users (and even veteran users) might not know about them. In this article, I'll review a few of the cool things that this task can do for you.

Read fixed-width text files into SAS

We think of CSV files (and...alas...Excel files) as the main standard for data exchange among systems, but many legacy systems still produce and consume fixed-width text data formats. The SAS DATA step is a perfect tool for reading these files, but defining the columns and their properties can be tedious. The "Fixed columns" option on the Import Data task can make this job simple.

Suppose that you're beginning with a spec like this:

And a raw data file like this:

You can use the Import Data wizard to define the boundaries of your columns by adding boundary lines with just click-and-drag operations. Beginning with the File->Import Data task, select your source text file and advance to the second page of the wizard. When you select "Fixed columns" as the input text format, you'll see a layout ruler that looks like this:

Click at the column boundaries (referring to your original spec!) and drag the rule lines as needed to define those column boundaries. Then click Next, and fill out details for the column names and types:

Which then tells the Import Data task how to generate the proper INPUT statements:

When you click Finish, you end up with a data set that's ready for business:

Modify the properties for multiple columns -- with one step

Here's a click-saving trick. Sometimes you have an input data file that contains many columns that share the same properties: type, length, and SAS format. It can be tedious to click and modify the properties of each column that you want to import. There's a shortcut on the Define Field Attributes page of the wizard that you can use to change the attributes for several columns at the same time. Simply SHIFT+Click to select multiple column definitions on the page, then click Modify.... The "Field Attributes for Multiple Selections" window appears, and you can change the necessary attributes just once and apply to the many items you picked.

This trick works as you import any text file or Excel file.

Create SAS program code that you can reuse anywhere

In a previous article I described how the Import Data task works "behind the scenes." Some of the magic that the task performs is not captured in SAS code, and that can present a challenge when you want to reuse this work in other settings -- for example, in a batch process or in a larger SAS program. However, with a couple of tweaks you can coerce the Import Data task into creating SAS code that you can almost just "lift and shift," as is.

The first option is hidden under the Performance window, labeled as "Bypass the data cleansing process." By default, the Import Data task reformats your input text file to normalize it for a cleaner import step. While doing no harm, most of the time this step isn't needed -- especially if your original data file is well formed. And since this step changes the input file, it's isn't repeatable outside of this task. My first tip for the best reusable code: click Performance... on the first page of the wizard, then select the "Bypass.." checkbox. That guarantees that the code will be formulated to read your original raw file. (Note that the Performance button is available only when importing text files, not Excel files.)

The second option you'll want to change is related to this, but you'll find it on the final page with the Advanced Options. Select "Generalize import step to run outside of SAS Enterprise Guide." This ensures that the task won't attempt any behind-the-scenes monkey business with your original file -- everything is captured in the DATA step that the task generates. Well, almost everything...

The one missing piece, a confounding factor when you select a local text file to import on a remote SAS Workspace session, is the transfer of the local file to the remote server. SAS Enterprise Guide copies the file for you -- behind the scenes -- and there is no SAS code to represent this step.

You can take control of even this step, though, if you make use of the Copy Files task (now available for you on the Tasks->Data menu). You can then copy the file from a local source folder, and land it wherever you want on the SAS server. Modify your newly repurposed Import Data code to pull from that server-based destination, giving you more control over the individual steps in the import process.

Learn more about importing text files

If you're new to importing data into SAS, whether using a SAS program or SAS Enterprise Guide, you might learn some of the basics from these video tutorials that were produced by SAS instructors:

12月 132017
 

I'm a big fan of the Import Data task in SAS Enterprise Guide, especially for its support of text-based files (CSV, tab delimited, fixed width, and more). There's no faster method for generating SAS code that reads your data exactly the way you need it. I use the tool so often that I take for granted some of its neatest features, and I forget that many new users (and even veteran users) might not know about them. In this article, I'll review a few of the cool things that this task can do for you.

Read fixed-width text files into SAS

We think of CSV files (and...alas...Excel files) as the main standard for data exchange among systems, but many legacy systems still produce and consume fixed-width text data formats. The SAS DATA step is a perfect tool for reading these files, but defining the columns and their properties can be tedious. The "Fixed columns" option on the Import Data task can make this job simple.

Suppose that you're beginning with a spec like this:

And a raw data file like this:

You can use the Import Data wizard to define the boundaries of your columns by adding boundary lines with just click-and-drag operations. Beginning with the File->Import Data task, select your source text file and advance to the second page of the wizard. When you select "Fixed columns" as the input text format, you'll see a layout ruler that looks like this:

Click at the column boundaries (referring to your original spec!) and drag the rule lines as needed to define those column boundaries. Then click Next, and fill out details for the column names and types:

Which then tells the Import Data task how to generate the proper INPUT statements:

When you click Finish, you end up with a data set that's ready for business:

Modify the properties for multiple columns -- with one step

Here's a click-saving trick. Sometimes you have an input data file that contains many columns that share the same properties: type, length, and SAS format. It can be tedious to click and modify the properties of each column that you want to import. There's a shortcut on the Define Field Attributes page of the wizard that you can use to change the attributes for several columns at the same time. Simply SHIFT+Click to select multiple column definitions on the page, then click Modify.... The "Field Attributes for Multiple Selections" window appears, and you can change the necessary attributes just once and apply to the many items you picked.

This trick works as you import any text file or Excel file.

Create SAS program code that you can reuse anywhere

In a previous article I described how the Import Data task works "behind the scenes." Some of the magic that the task performs is not captured in SAS code, and that can present a challenge when you want to reuse this work in other settings -- for example, in a batch process or in a larger SAS program. However, with a couple of tweaks you can coerce the Import Data task into creating SAS code that you can almost just "lift and shift," as is.

The first option is hidden under the Performance window, labeled as "Bypass the data cleansing process." By default, the Import Data task reformats your input text file to normalize it for a cleaner import step. While doing no harm, most of the time this step isn't needed -- especially if your original data file is well formed. And since this step changes the input file, it's isn't repeatable outside of this task. My first tip for the best reusable code: click Performance... on the first page of the wizard, then select the "Bypass.." checkbox. That guarantees that the code will be formulated to read your original raw file. (Note that the Performance button is available only when importing text files, not Excel files.)

The second option you'll want to change is related to this, but you'll find it on the final page with the Advanced Options. Select "Generalize import step to run outside of SAS Enterprise Guide." This ensures that the task won't attempt any behind-the-scenes monkey business with your original file -- everything is captured in the DATA step that the task generates. Well, almost everything...

The one missing piece, a confounding factor when you select a local text file to import on a remote SAS Workspace session, is the transfer of the local file to the remote server. SAS Enterprise Guide copies the file for you -- behind the scenes -- and there is no SAS code to represent this step.

You can take control of even this step, though, if you make use of the Copy Files task (now available for you on the Tasks->Data menu). You can then copy the file from a local source folder, and land it wherever you want on the SAS server. Modify your newly repurposed Import Data code to pull from that server-based destination, giving you more control over the individual steps in the import process.

Learn more about importing text files

If you're new to importing data into SAS, whether using a SAS program or SAS Enterprise Guide, you might learn some of the basics from these video tutorials that were produced by SAS instructors:

12月 052017
 

The internet is rich with data, and much of that data seems to exist only on web pages, which -- for some crazy reason -- are designed for humans to read. When students/researchers want to apply data science techniques to analyze collect and analyze that data, they often turn to "data scraping." What is "data scraping?" I define it as using a program to fetch the contents of a web page, sift through its contents with data parsing functions, and save its information into data fields with a structure that facilitates analysis.

Python and R users have their favorite packages that they use for scraping data from the web. New SAS users often ask whether there are similar packages available in the SAS language, perhaps not realizing that Base SAS is already well suited to this task -- no special bundles necessary.

The basic steps for data scraping are:

  1. Fetch the contents of the target web page
  2. Process the source content of the page -- usually HTML source code -- and parse/save the data fields you need.
  3. If necessary, repeat for subsequent pages. This applies to those web sites that serve up lots of information in paginated form, and you want to collect all available pages of data.

Let's map these steps to the SAS programming language:

For this step Use these features
Get the contents of the web page FILENAME URL
Process/parse the web page contents DATA step, with parsing functions such as FIND, regular expressions via PRXMATCH. Use SAS informats to convert text to native data types.
Repeat across subsequent pages SAS macro language (%DO %UNTIL processing) or DATA step with asked about scraping data from the Center for Disease Control (CDC) web site.

Step 0: Find the original data source and skip the scrape

I'm writing this article at the end of 2017, and at this point in our digital evolution, web scraping seems like a quaint pastime. Yes, there are still some cases for it, which is why I'm presenting this article. But if you find information that looks like data on a web page, then there probably is a real data source behind it. And with the movement toward open data (especially in government) and data-driven APIs (in social media and commercial sites) -- there is probably a way for you to get to that data source directly.

In my example page for this post, the source page is hosted by CDC.gov, a government agency whose mandate is to share information with other public and private entities. As one expert pointed out, the CDC shares a ton of data at its dedicated data.cdc.gov site. Does this include the specific table the user wanted? Maybe -- I didn't spend a lot of time looking for it. However, even if the answer is No -- it's a simple process to ask for it. Remember that web sites are run by people, and usually these people are keen to share their information. You can save effort and achieve a more reliable result if you can get the official data source.

If you find an official data source to use instead, you might still want to automate its collection and import into SAS. Here are two articles that cover different techniques:

Web scraping is lossy, fragile process. The information on the web page does not include data types, lengths, or constraints metadata. And one tweak to the presentation of the web page can break any automated scraping process. If you have no other alternative and you're willing to accept these limitations, let's proceed to Step 1.

Step 1: Fetch the web page

In this step, we want to achieve the equivalent of the "Save As..." function from your favorite web browser. Just like your web browser, SAS can act as an HTTP client. The two most popular methods are:

  • FILENAME URL, which assigns a SAS fileref to the web page content. You can then use INFILE and INPUT to read that file from a DATA step.
  • PROC HTTP, which requires a few more lines of code to get and store the web page content in a single SAS procedure step.

I prefer PROC HTTP, and here's why. First, as a separate explicit step it's easier to run just once and then work with the file result over the remainder of your program. You're guaranteed to fetch the file just once. Though a bit more code, it makes it more clear about what happens under the covers. Next reason: PROC HTTP has been refined considerably in SAS 9.4 to run fast and efficient. Its many options make it a versatile tool for all types of internet interactions, so it's a good technique to learn. You'll find many more uses for it.

Here's my code for getting the web page for this example:

/* Could use this, might be slower/less robust */
* filename src url "https://wwwn.cdc.gov/nndss/conditions/search/";
 
/* I prefer PROC HTTP for speed and flexibility */
filename src temp;
proc http
 method="GET"
 url="https://wwwn.cdc.gov/nndss/conditions/search/"
 out=src;
run;

Step 2: Parse the web page contents

Before you can write code that parses a web page, it helps to have some idea of how the page is put together. Most web pages are HTML code, and the data that is "locked up" within them are expressed in table tags: <table>, <tr>, <td> and so on. Before writing the first line of code, open the HTML source in your favorite text editor and see what patterns you can find.

For this example from the CDC, the data we're looking for is in a table that has 6 fields. Fortunately for us, the HTML layout is very regular, which means our parsing code won't need to worry about lots of variation and special cases.

 <tr >
	<td style="text-align:left;vertical-align:middle;">
    	<a href="/nndss/conditions/chancroid/">
		Chancroid
		</a>
	</td>
	<td class="tablet-hidden"></td>
	<td class="tablet-hidden"></td>
	<td class="tablet-hidden"><i>Haemophilus ducreyi</i></td>
	<td  >1944 </td>
	<td  > Current</td>
</tr>

The pattern is simple. We can find a unique "marker" for each entry by looking for the "/nndss/conditions" reference. The line following (call it offset-plus-1) has the disease name. And the lines that are offset-plus-7 and offset-plus-8 contain the date ranges that the original poster was asking for. (Does this seem sort of rough and "hacky?" Welcome to the world of web scraping.)

Knowing this, we can write code that does the following:

Here's the code that I came up with, starting with what the original poster shared:

/* Read the entire file and skip the blank lines */
/* the LEN indicator tells us the length of each line */
data rep;
infile src length=len lrecl=32767;
input line $varying32767. len;
 line = strip(line);
 if len>0;
run;
 
/* Parse the lines and keep just condition names */
/* When a condition code is found, grab the line following (full name of condition) */
/* and the 8th line following (Notification To date)                                */
/* Relies on this page's exact layout and line break scheme */
data parsed (keep=condition_code condition_full note_to);
 length condition_code $ 40 condition_full $ 60 note_to $ 20;
 set rep;
 if find(line,"/nndss/conditions/") then do;
   condition_code=scan(line,4,'/');
   pickup= _n_+1 ;
   pickup2 = _n_+8;
   /* "read ahead" one line */
   set rep (rename=(line=condition_full)) point=pickup;
   condition_full = strip(condition_full);
 
   /* "read ahead" 8 lines */
   set rep (rename=(line=note_to)) point=pickup2;
   /* use SCAN to get just the value we want */
   note_to = strip(scan(note_to,2,'<>'));
 
   /* write this record */
   output;
  end;
run;

The result still needs some cleanup -- there are a few errant data lines in the data set. However, it should be simple to filter out the records that don't make sense with some additional conditions. I left this as a to-do exercise to the original poster. Here's a sample of the result:

Step 3: Loop and repeat, if necessary

For this example with the CDC data, we're done. All of the data that we need appears on a single page. However, many web sites use a pagination scheme to break the data across multiple pages. This helps the page load faster in the browser, but it's less convenient for greedy scraping applications that want all of the data at once. For web sites that paginate, we need to repeat the process to fetch and parse each page that we need.

Web sites often use URL parameters such as "page=" to indicate which page of results to serve up. Once you know the URL parameter that controls this offset, you can create a SAS macro program that iterates through all of the pages that you want to collect. There are many ways to accomplish this, but I'll share just one here -- and I'll use a special page from the SAS Support Communities (communities.sas.com) as a test.

Aside from the macro looping logic, my code uses two tricks that might be new to some readers.

/* Create a temp folder to hold the HTML files */
options dlcreatedir;
%let htmldir = %sysfunc(getoption(WORK))/html;
libname html "&htmldir.";
libname html clear;
 
/* collect the first few pages of results of this site */
%macro getPages(num=);
 %do i = 1 %to &num.;
   %let url = https://communities.sas.com/t5/custom/page/page-id/activity-hub?page=&i.;
   filename dest "&htmldir./page&i..html";
   proc http 
      method="GET"
	  url= "&url."
	  out=dest;
   run;
 %end;
%mend;
 
/* How many pages to collect */
%getPages(num=3);
 
/* Use the wildcard feature of INFILE to read all */
/* matching HTML files in a single step */
data results;
 infile "&htmldir./*.html" length=len lrecl=32767;
 input line $varying32767. len ;
 line = strip(line);
 if len>0;
run;

The result of this program is one long data set that contains the results from all of the pages -- that is, all of lines of HTML code from all of the web pages. You can then use a single DATA step to post-process and parse out the data fields that you want to keep. Like magic, right?

Where to learn more

Each year there are one or two "web scraping" case studies presented at SAS Global Forum. You can find them easily by searching on the keyword "scrape" from the SAS Support site or from lexjansen.com. Here are some that I found interesting:

How JMP 13 sees the CDC page

If you have access to JMP software from SAS, you could try the File->Internet Open... feature. It's useful for a one-time, ad-hoc parsing step that you can later capture into a JSL script for future use. With Internet Open you can specify a URL and select to open it "as HTML," and then JMP will offer a selection of available tables to import as data. This is definitely worth a try if you have JMP on your desktop, as it can lend some insight about the structure of the page you're trying to scrape.

JMP 13 version of the data, imported

The post How to scrape data from a web page using SAS appeared first on The SAS Dummy.

11月 222017
 

Social media has brought anniversary dates to the forefront. Every day, my view of Google Photos or Facebook shows me a collection of photos from exactly some number of years ago to remind me of how good things were back then. These apps are performing the simplest of date-based math to trick me. They think, "We have a collection of photos from this month/day from some previous year -- let's collect those together and then prey on Chris' nostalgia instincts."

Sometimes it works and I share them with friends. Weren't my kids so cute back then? It's true, they were -- but I don't pine for those days. I live in the present and I look towards the future -- as all citizens of the world should.

Looking back at dates in SAS

But you know who likes to look at the past? Managers (and probably a few of my colleagues...). Many of my SAS jobs are in support of date-based reports. They answer questions such as, "what happened in the past 30 days?" or "how much activity in the past 6 months?" When I have SAS date values, going back 30 days is simple. The SAS date for "30 days ago" is simply today()-30. (Because, remember, a SAS date is simply an integer representing the count of days since Jan 1, 1960.)

"6 months ago" is a little more nuanced. I could fudge it by subtracting 183 or 184 from the value of today(), but that's not precise enough for the analytical wonks that I work with. Fortunately, SAS provides a function that can compute any date -- given a starting date -- using just about any criteria you can imagine. The function is called INTNX (

six_mo_ago = intnx(
 'month',  /* unit of time interval */
  today(), /* beginning date */
  -6,      /* number of intervals, negative goes to the past */
  'same'   /* alignment of interval date. "Same" is for same day of month */
 );

If I wanted to go 6 years into the past, I would simply change that first argument to 'year'. 6 days? Change it to 'day'. in %SYSFUNC -- the macro function that breaks out of macro processing to invoke built-in SAS functions. I also need to remove the quotes around the interval and alignment values -- the SAS macro processor will treat these as string literals and would not approve of the quotes.

%let six_mo_ago = 
 %sysfunc(
  intnx(
   month,             /* unit of time interval */
   %sysfunc(today()), /* function to get current date */
   -6,                /* number of intervals, negative goes to the past */
   same               /* alignment of interval date. "Same" is for same day of month */
   )
  );
 %put &=six_mo_ago;

Output (from today's run):

45          %put &=six_mo_ago;
SIX_MO_AGO=20960

Notice the result is a number -- which is exactly what I need for date value comparisons. But what if I wanted a formatted version of the date? The

 %let six_mo_ago_fmt = 
  %sysfunc(
   intnx(
   month,             /* unit of time interval */
   %sysfunc(today()), /* function to get current date */
   -6,                /* number of intervals, negative goes to the past */
   same               /* alignment of interval date. "Same" is for same day of month */
   ), date9.  /* Tell %SYSFUNC how to format the result */
  );
 %put &=six_mo_ago_fmt;

Output (from today's run):

56          %put &=six_mo_ago_fmt;
SIX_MO_AGO_FMT=21MAY2017

Adapting INTNX for SAS datetime values

This computed date works perfectly when my data sets contain SAS date values that I want to filter. For example, I can limit the records to those from the past 6 months with code similar to this:

proc freq data=comm.visits (where=(date > &six_mo_ago.)) 
 noprint 
 ;
tables geo_country_code / out=country_visits_6mo;
run;

But I realized that some of my data sets use datetime values, not date values. A SAS datetime value is simply the number of seconds since midnight on Jan 1, 1960. I've seen programs that adapt the code above by converting the computed cutoff date from a date value to a datetime value -- it's simple to do with math:

proc freq data=comm.visits (where=(event_time > %sysevalf( &six_mo_ago. * 60 * 60 * 24 ))) 
 noprint 
 ;
tables geo_country_code / out=country_visits_6mo;
run;

The

%let six_mo_ago_dt = 
 %sysfunc(
  intnx(
   dtmonth,              /* unit of time interval */
   %sysfunc(datetime()), /* function to get current datetime */
   -6,                   /* number of intervals, negative goes to the past */
   same                  /* alignment of interval date. "Same" is for same day of month/same time */
   )
  );
 %put &=six_mo_ago_dt;

Output (at this moment):

34          %put &=six_mo_ago_dt;
SIX_MO_AGO_DT=1810991917.93526

I can then use the much more readable version of my comparison code:

proc freq data=comm.visits (where=(event_time > &six_mo_ago_dt)) 
 noprint 
 ;
tables geo_country_code / out=country_visits_6mo;
run;

Learning more about date and datetime intervals

First, an acknowledgment. I recently had the privilege of presenting at the Quebec user groups with SAS-world superstar Marje Fecht. Marje is an excellent instructor, and she delivered a popular talk about working with SAS date values -- a topic that trips up many new SAS users. It's useful for me -- someone who has used SAS for a long time -- to see a basic topic presented to me as if I were brand new. Her talk reminded me of some good practices that I was able to bring home and apply in my own production SAS jobs. I know that Marje was invited to present this talk at SAS Global Forum 2018, and I hope that I did not steal too much of her thunder. She has a good origin story about the 01JAN1960 date decision. You should attend the conference and see her talk in person.

The INTNX (and its sister function for computing date differences, INTCK) are powerful tools for manipulating date and datetime values. Other programming languages offer complex code libraries to accomplish what these two functions can do as part of Base SAS. They are tricky to learn at first, but once you get the hang of them they can really simplify your SAS programs that deal with time-based data.

Rick Wicklin presented a useful introduction to both functions in INTCK and INTNX: Two essential functions for computing intervals between dates in SAS.

While these functions are available in Base SAS, they are maintained by the developers who look after SAS/ETS (econometrics and time series). You'll find see this thread on the SAS Support Communities.

The post Computing a date from the past (or future) with SAS appeared first on The SAS Dummy.

10月 102017
 

Remember when 100MB was large?

SAS 9.4 Maintenance 5 includes new support for reading and writing GZIP files directly. GZIP files, usually found with a .gz file extension, are a different format than ZIP files. Although both are forms of compressed files, a GZIP file is usually a compressed copy of a single file, whereas a ZIP file is an "archive" -- a collection of files in a compressed virtual folder. GZIP tools are built into Unix/Linux platforms and are commonly used to save space when storing large text-based files that you're not ready to part with: log files, csv files, and more. The algorithm used to compress GZIP files performs especially well with text files, although you can technically GZIP any file that you want.

I've written extensively about using FILENAME ZIP to read and write ZIP archives with SAS. The latest version of filename my_gz ZIP "path-to-file/compressedfile.txt.gz" GZIP;

Here's an example that creates a compressed version of a log file:

filename source "C:\Logs\SEGuide_log.10168.txt";
filename tozip ZIP "C:\Logs\SEGuide_log.10168.txt.gz" GZIP;
 
data _null_;   
   infile source;
   file tozip ;
   input;
   put _infile_ ;
run;

In my test here, the result represents a significant size difference, with the compressed file occupying just 14% of the space.


To "re-inflate" the compressed file, we can perform the opposite operation. (I added the ENCODING option here because I know my log file was UTF-8 encoded.)

filename target "C:\LogsExpanded\SEGuide_log.10168.txt" encoding='utf-8';
filename fromzip ZIP "C:\Logs\SEGuide_log.10168.txt.gz" GZIP;
 
data _null_;   
   infile fromzip;
   file target ;
   input;
   put _infile_ ;
run;

You don't have to explicitly expand a compressed text file in order to read it with SAS. You can use the GZIP method to read and parse a .gz file directly, similar to the zcat command that you might be familiar with from the Unix shell:

filename fromzip ZIP "C:\Logs\SEGuide_log.10168.txt.gz" GZIP;
data logdata;   
   infile fromzip; /* read directly from compressed file */
   input  date : yymmdd10. time : anydttme. ;
   format date date9. time timeampm.;
run;

If your file is in a binary format such as a SAS data set (sas7bdat) or Excel (XLS or XLSX), you probably will need to expand the file completely before reading it as data. These files are read using special drivers that don't process the bytes sequentially, so you need the entire file available on disk.

Note: Because each GZIP file represents just one compressed file, the MEMBER= option doesn't apply. When dealing with ZIP file archives that contain multiple files, you could use the MEMBER= option on FILENAME ZIP to address a specific file that you want. My recent example about FINFO and file details relies heavily on that approach. However, the GZIP option and MEMBER= options are mutually exclusive. In that way, it's much simpler...just like its Unix shell equivalent.


* ZIP drive image By © Raimond Spekking / CC BY-SA 4.0 (via Wikimedia Commons), CC BY-SA 4.0, Link

The post Reading and writing GZIP files with SAS appeared first on The SAS Dummy.

9月 082017
 

It's time to share another tip about working with ZIP files in SAS. Since I first wrote about FILENAME ZIP to list and extract files from a ZIP archive, readers have been asking for more. Specifically, they want additional details about the files that are contained in a ZIP, including the original file datetime stamps, file size, and compressed size. Thanks to a feature that was quietly added into SAS 9.4 Maintenance 3, you can use the FINFO function to retrieve these details. In this article, I share a SAS macro program that does the job.

Here's an abridged example of the output. If you need to create something like this without the use of external ZIP tools like 7-Zip or WinZip (which are often unavailable in controlled environments), read on.

FILENAME ZIP output

You can download the full program from my public gist on GitHub: zipfiles_list_details.sas

ZIPpy details: a solution in three macros

Here's my basic approach to this problem:

  • First, create a list of all of the ZIP files in a directory and all of the file "members" that are compressed within. I've already shared this technique in a previous article. Like an efficient (or lazy) programmer, I'm just reusing that work. That's macro routine #1 (%listZipContents).
  • With this list in hand, iterate through each ZIP file member, "open" the file with FOPEN, and gather all of the available file attributes with FINFO. I've divided this into two macros for readability. %getZipMemberInfo (macro routine #2) retrieves all of the file details for a single member and stores them in a data set. %getZipDetails (macro routine #3) iterates through the list of ZIP file members, calls %getZipMemberInfo on each, and concatenates the results into a single output data set.

Here's a sample usage:

  %listzipcontents (targdir=C:\Projects\ZIPPED_Examples, outlist=work.zipfiles);
  %getZipDetails (inlist=work.zipfiles, outlist=work.zipdetails);

I tried to add decent comments to my program so that interested coders can study and adapt as needed. Here's a snippet of code that uses the FINFO function, which is really the important part for retrieving these file details.

/*
 Assumes an assignment like:
  FILENAME F ZIP "C:\ZIPPED_Examples\SudokuSolver_src.zip" member="src/AboutThisProject.txt";
*/
fId = fopen("&f","S");
if fID then
  do;
   infonum=foptnum(fid);
     do i=1 to infonum;
      infoname=foptname(fid,i);
      select (infoname);
       when ('Filename') filename=finfo(fid,infoname);
       when ('Member Name') membername=finfo(fid,infoname);
       when ('Size') filesize=input(finfo(fid,infoname),15.);
       when ('Compressed Size') compressedsize=input(finfo(fid,infoname),15.);
       when ('CRC-32') crc32=finfo(fid,infoname);
       when ('Date/Time') filetime=input(finfo(fid,infoname),anydtdtm.);
      end;    
   end;
 compressedratio = compressedsize / filesize;
 output;
 fId = fClose( fId );

The FINFO function in SAS provides access to file attributes and their values for a given file that you've accessed using the FOPEN function. The available file attributes can differ according to the type of file (FILENAME access method) that is used. ZIP files, as you can guess, have some attributes that are specific to them: "Compressed Size", "CRC-32", and others. This code checks for all of the available attributes and keeps those that we need for our detailed output. (And see the use of the SELECT/WHEN statement? So much more readable than a bunch of IF/THEN/ELSEs.)

Look, I'm not going to claim that my approach to this problem is the most elegant or most efficient -- but it works. If it can be improved, then I'm sure I'll hear from a few of you experts out there. Bring it on!

For more about ZIP files in SAS

The post Using FILENAME ZIP and FINFO to list the details in your ZIP files appeared first on The SAS Dummy.

8月 262017
 

News flash: My favorite SAS code editor is SAS Enterprise Guide. However, my favorite general purpose text editor is Notepad++, and I often find myself using that tool for viewing SAS log files and for making small modifications to SAS programs. Judging from the popularity of this SAS Support Communities discussion, I'm not alone. In this post, I'll share the steps for turning Notepad++ into a more useful home for SAS programs.

You can download Notepad++ for Windows from here -- you can use it for free, no cost. That's one reason that it's one of the first tools that I install on any new PC I get my hands on!

1. Associate SAS files with Notepad++

You accomplish this in the usual way with Windows. In Windows Explorer, right-click (or SHIFT+right-click depending on your setup) on a .SAS file (SAS program), and select Open with...

Open with menu

► You might see Notepad++ in the menu, but don't select it, Instead, select Choose another app.

Choose another app

► In this menu, select Notepad++ and check the "Always use this app" checkbox.

Repeat this step for SAS program logs (.LOG files) and listing output (.LST files) if you want.

2. Run a SAS program from Notepad++

You can add new program actions to the Run menu in Notepad++. Here's how to add a command to run a SAS program, if you have SAS for Windows installed. Note: These steps assume that the SAS program is open in Notepad++ and is saved in a file.

► Select Run... from the Run menu. In the program to run field, enter this command:

"C:\Program Files\SASHome\SASFoundation\9.4\sas.exe" -sysin "$(FULL_CURRENT_PATH)" -log "$(CURRENT_DIRECTORY)\$(NAME_PART).log" -print "$(CURRENT_DIRECTORY)\$(NAME_PART).lst"

all on one line. You might need to adjust the SAS.EXE path for your install. The command options use some Notepad++ environment variables to direct the SAS log and listing output to the same path as the SAS program file.

Click Save (not Run).

Click Save

Optionally, assign a shortcut key to the action, and name it "Run program file in SAS" (or whatever you want). This adds the command to your Run menu.

Run in SAS command

When you select it, Notepad++ will launch SAS, run your program in batch, and direct the output to the same folder where the program is stored.

3. Adding SAS syntax color coding to Notepad++

It's simple to "teach" Notepad++ to recognize the keywords from SAS and other languages. You can download new language definitions files from here -- follow the instructions on the page to have your Notepad++ recognize them. I've created an expanded definition file that includes more SAS keywords (many, many more!) -- you can grab that from my GitHub repo here.

Here's what SAS code looks like in my Notepad++:

my Notepad++ SAS look

What else?

I'm sure that some of you have spent more time than I have in creating a souped-up Notepad++ environment, or perhaps you've taken it to another level with other popular editors like Sublime or Vim or UltraEdit. If you have other tips to share, I'd love to hear from you in the comments.

The post Using Notepad++ as your SAS code editor appeared first on The SAS Dummy.

8月 192017
 

SAS programmers have high expectations for their coding environment, and why shouldn't they? Companies have a huge investment in their SAS code base, and it's important to have tools that help you understand that code and track changes over time. Few things are more satisfying as a SAS program that works as designed and delivers perfect results. (Oh, hyperbole you say? I don't think so.) But when your program isn't working the way it should, there are two features that can help you get back on track: a code debugger, and program revision history. Both of these capabilities are built into SAS Enterprise Guide. Program history was added in v7.1, and the debugger was added in v7.13.

I've written about the DATA step debugger before -- both as a teaching tool and as a productivity tool. In this article, I'm sharing a demo of the debugger's features, led by SAS developer Joe Flynn. Before joining the SAS Enterprise Guide development team, Joe worked in SAS Technical Support. He's very familiar with "bugs," and reported his share of them to SAS R&D. Now -- like every programmer -- Joe makes the bugs. But of course, he fixes most of them before they ever see the light of day. How does he do that? Debugging.

This video is only about 8 minutes long, but it's packed with good information. In the debugger demo, you'll learn how you can use standard debugging methods, such as breakpoints, step over and step through, watch variables, jump to, evaluate expression, and more. There is no better way to understand exactly what is causing your DATA step to misbehave.

Joe's debugger

In the program history demo (the second part of the video), you'll learn how team members can collaborate using standard source management tools (such as Git). If you establish a good practice of storing code in a central place with solid source management techniques, SAS Enterprise Guide can help you see who changed what, and when. SAS Enterprise Guide also offers a built-in code version comparison tool, which enhances your ability to find the breaking changes. You can also use the code comparison technique on its own, outside of the program history feature.

program history

Take a few minutes to watch the video, and then try out the features yourself. You don't need a Git installation to play with program history at the project level, though it helps when you want to extend that feature to support team collaboration.

See also

The post Code debugging and program history in SAS Enterprise Guide appeared first on The SAS Dummy.

8月 052017
 

Many years ago I shared a custom task that allows you to view and manage SAS catalogs within SAS Enterprise Guide. As a reminder, a SAS catalog is a special type of SAS file that acts as a container, like a folder, for a variety of content items called catalog entries. Conceptually, this is like a folder in a file system with a diverse collection of file types. The entries and their uses are specific to SAS, and they include things like SOURCE (program code), SCL (SAS Component Language) programs, SAS format definitions, compiled SAS macro programs, and more.

Most catalog entries aren't that interesting for SAS Enterprise Guide users -- with the notable exception of SAS format definitions. Beginning with the day that I shared the task on my blog, my readers began asking for the ability to see inside a SAS format definition. I replied, "great idea! I'll put that on my to-do list." Here it is, 7 years later -- and I've got it done. I am a man of my word.

>> Download the task from here (ZIP file, with README text file instructions)

Pay special attention to the README instructions to deploy the correct version, and make sure you unblock the task DLL if needed.

And more good news -- this task is scheduled to ship as a built-in task (on the Tools menu) in the next release of SAS Enterprise Guide, coming soon. The custom task download will support SAS Enterprise Guide versions 4.3 through 7.13. Later versions will have the task already available!

Key features of the Catalog and Formats Explorer

Like the original task that I shared, this revised task allows you to explore SAS libraries and the catalogs that they contain. You can view the metadata about every catalog entry -- name, type, description, created and modified dates. You can also delete entries that you need to clean up. And you can view the contents of certain types of entries, mainly SOURCE entries and the format entry types. Here are the main enhancements:

  • You can view the contents of FORMAT, FORMATC, INFMT, INFMTC entries
  • The window displays “modeless” – you can have it showing while you still work on other tasks, such as your SAS programs. Define a format in code, run it, and then click Refresh in the task to see it.
  • You'll find special entry for "User-defined formats", which shows just the catalogs that contain format definitions within the FMTSEARCH path. You can still navigate any library to find catalogs that contain formats.

Here's a screenshot of the task. You can also watch my 5-minute video demonstration of the task in action on SAS Support Communities.

For developers: How it was built

Since the original task was shared as a companion example for my custom tasks book, I've also shared the source code that goes into these enhancements. You can find these in the same GitHub repository that contains the original version. Adding these enhancements makes use of other techniques that I've already shared on this blog.

To generate a text report of a format definition, the task submits a PROC FORMAT step with a SELECT statement. Here's an example:

filename fmtout temp;  
%let _catexp1 = %sysfunc(getoption(center));  
%let _catexp2 = %sysfunc(getoption(date));  
%let _catexp3 = %sysfunc(getoption(number));  
%let _catexp4 = %sysfunc(getoption(pagesize));  
options nocenter nodate nonumber pagesize=max;  
ods listing file=fmtout; 
proc format fmtlib lib=dbmlnx.formats; 
 select ab; 
run; 
ods listing close; 
options &_catexp1. &_catexp2. &_catexp3. PS=&_catexp4.;

With the listing file generated, the task uses code similar to this Windows PowerShell example to download the contents of the output. You might notice that the SAS code sets a few system options, but it stores and later replaces the option values so that your other programs won't be affected.

If you like to tinker, feel free to download the source code and see how it works. If you want to learn more about custom tasks in SAS Enterprise Guide, view my Ask the Expert session, available on-demand from here.

The post Viewing SAS catalogs and formats in SAS Enterprise Guide appeared first on The SAS Dummy.

7月 262017
 

recently used filesI use SAS Enterprise Guide every day, and for a wide variety of tasks. As a result, I have a huge collection of project files (EGP files) and SAS program files.

I have always relied on the "recently used" list in the File menu to provide me with quick access to the files I need to open. The File menu keeps two lists: one for project files and one for SAS programs. With a click into either list, you can see a simple list of all of the file names. Hover your cursor over any of the names to see a tooltip with the full path -- very useful in case you have similarly named files in different folders.

By default, the number of recently used items (SAS programs and projects) that the File menu tracks is just 6. That's not nearly as many as I need, so I've always changed that setting to the maximum, which has always been 15. You can find the setting in Tools→Options, General tab.

I recently learned that in SAS Enterprise Guide v7.12 and later, the maximum number of "recent files" to track was raised to 50! With more high-resolution displays in the field, even a long list of recent files can provide a convenient method to save a few clicks. I think 50 is a little high for me (I'll be reaching into last year's files), so I bumped my setting to 30.

Get EG to remember more files for you!

And don't forget the "jump list" in Windows 7 and later! Every program or project file that SAS Enterprise Guide opens is also added to this quick-access list that you can find on your Windows toolbar or Start menu. Here's an example of what that looks like:

jump list in Windows

The post Open recent files with fewer clicks in SAS Enterprise Guide appeared first on The SAS Dummy.