Even with the limited character set, pictorial communication was possible with ASCII through the fun medium of "ASCII art." ASCII art is basically the stone-age version of emojis. For example, consider the shrug emoji: 🤷

Its text-art ancestor (not strictly ASCII as a sharp reader pointed out) is this: ¯\_(ツ)_/¯ While ASCII and text art currently enjoys a retro renaissance, the emoji has become indispensable in our daily communications.

Emojis before Unicode

Given the ubiquity of emojis in every communication channel, it's sometimes difficult to remember that just a few years ago emoji characters were devised and implemented in vendor-specific offerings. As the sole Android phone user in my house, I remember a time when my iPhone-happy family could express themselves in emojis that I couldn't read in the family group chat. Apple would release new emojis for their users, and then Android (Google) would leap frog with another set of their own fun symbols. But if you weren't trading messages with users of the same technology, then chunks of your text would be lost in translation.

Enter Unicode. A standard system for encoding characters that allows for multiple bytes of storage, Unicode has seemingly endless runway for adding new characters. More importantly, there is a standards body that sets revisions for Unicode characters periodically so everyone can use the same huge alphabet. In 2015, emoji characters were added into Unicode and have been revised steadily with universal agreement.

This standardization has helped to propel emojis as a main component of communication in every channel. Text messages, Twitter threads, Venmo payments, Facebook messages, Slack messages, GitHub comments -- everything accepts emojis. (Emojis are so ingrained and expected that if you send a Venmo payment without using an emoji and just use plain text, it could be interpreted as a slight or at the least as a miscue.)

For more background about emojis, read How Emjois Work (source: How Stuff Works).

Unicode is essential for emojis. In SAS, the use of Unicode is possible by way of UTF-8 encoding. If you work in a modern SAS environment with a diverse set of data, you should already be using ENCODING=UTF8 as your SAS session encoding. If you use SAS OnDemand for Academics (the free environment for any learner), this is already set for you. And SAS Viya offers only UTF-8 -- which makes sense, because it's the best for most data and it's how most apps work these days.

Emojis as data and processing in SAS

Emojis are everywhere, and their presence can enrich (and complicate) the way that we analyze text data. For example, emojis are often useful cues for sentiment (smiley face! laughing-with-tears face! grimace face! poop!). It's not unusual for a text message to be ALL emojis with no "traditional" words.

The website Unicode.org maintains the complete compendium of emojis as defined in the latest standards. They also provide the emoji definitions as data files, which we can easily read into SAS. This program reads all of the data as published and adds features for just the "basic" emojis:

/* MUST be running with ENCODING=UTF8 */
filename raw temp;
proc http
 url="https://unicode.org/Public/emoji/13.1/emoji-sequences.txt"
 out=raw;
run;
 
ods escapechar='~';
data emojis (drop=line);
length line $ 1000 codepoint_range $ 45 val_start 8 val_end 8 type $ 30 comments $ 65 saschar $ 20 htmlchar $ 25;
infile raw ;
input;
line = _infile_;
if substr(line,1,1)^='#' and line ^= ' ' then do;
 /* read the raw codepoint value - could be single, a range, or a combo of several */
 codepoint_range = scan(line,1,';');
 /* read the type field */
 type = compress(scan(line,2,';'));
 /* text description of this emoji */
 comments = scan(line,3,'#;');
 
 /* for those emojis that have a range of values */
 val_start = input(scan(codepoint_range,1,'. '), hex.);
 if find(codepoint_range,'..') > 0 then do;
  val_end = input(scan(codepoint_range,2,'.'), hex.);
 end;
 else val_end=val_start;
 
 if type = "Basic_Emoji" then do;
  saschar = cat('~{Unicode ',scan(codepoint_range,1,' .'),'}');
  htmlchar = cats('<span>&#x',scan(codepoint_range,1,' .'),';</span>');
 end;
 output;
end;
run;
 
proc print data=emojis; run;

(As usual, all of the SAS code in this article is available on GitHub.)

The "features" I added include the Unicode representation for an emoji character in SAS, which could then be used in any SAS report in ODS or any graphics produced in the SG procedures. I also added the HTML-encoded representation of the emoji, which uses the form &#xNNNN; where NNNN is the Unicode value for the character. Here's the raw data view:

When you PROC PRINT to an HTML destination, here's the view in the results browser:

In search of structured emoji data

The Unicode.org site can serve up the emoji definitions and codes, but this data isn't exactly ready for use within applications. One could work through the list of emojis (thousands of them!) and tag these with descriptive words and meanings. That could take a long time and to be honest, I'm not sure I could accurately interpret many of the emojis myself. So I began the hunt for data files that had this work already completed.

I found the GitHub/gemoji project, a Ruby-language code repository that contains a structured JSON file that describes a recent collection of emojis. From all of the files in the project, I need only one JSON file. Here's a SAS program that downloads the file with PROC HTTP and reads the data with the JSON libname engine:

filename rawj temp;
 proc http
  url="https://raw.githubusercontent.com/github/gemoji/master/db/emoji.json"
  out=rawj;
run;
 
libname emoji json fileref=rawj;

Upon reading these data, I quickly realized the JSON text contains the actual Unicode character for the emoji, and not the decimal or hex value that we might need for using it later in SAS.

I wanted to convert the emoji character to its numeric code. That's when I discovered the UNICODEC function, which can "decode" the Unicode sequence into its numeric values. (Note that some characters use more than one value in a sequence).

Here's my complete program, which includes some reworking of the tags and aliases attributes so I can have one record per emoji:

filename rawj temp;
 proc http
  url="https://raw.githubusercontent.com/github/gemoji/master/db/emoji.json"
  out=rawj;
run;
 
libname emoji json fileref=rawj;
 
/* reformat the tags and aliases data for inclusion in a single data set */
data tags;
 length ordinal_root 8 tags $ 60;
 set emoji.tags;
 tags = catx(', ',of tags:);
 keep ordinal_root tags;
run;
 
data aliases;
 length ordinal_root 8 aliases $ 60;
 set emoji.aliases;
 aliases = catx(', ',of aliases:);
 keep ordinal_root aliases;
run;
 
/* Join together in one record per emoji */
proc sql;
 create table full_emoji as 
 select  t1.emoji as emoji_char, 
    unicodec(t1.emoji,'esc') as emoji_code, 
    t1.description, t1.category, t1.unicode_version, 
    case 
     when t1.skin_tones = 1 then  t1.skin_tones
	 else 0
	end as has_skin_tones,
    t2.tags, t3.aliases
  from emoji.root t1
  left join tags t2 on (t1.ordinal_root = t2.ordinal_root)
  left join aliases t3 on (t1.ordinal_root = t3.ordinal_root)
 ;
quit;
 
proc print data=full_emoji; run;

Here's a snippet of the report that includes some of the more interesting sequences:

The diversity and inclusion aspect of emoji glyphs is ever-expanding. For example, consider the emoji for "family":

• The basic family emoji code is \u0001F46A (👪)
• But since families come in all shapes and sizes, you can find a family that better represents you. For example, how about "family: man, man, girl, girl"? The code is \u0001F468\u200D\u0001F468\u200D\u0001F467\u200D\u0001F467, which includes the codes for each component "member" all smooshed together with a "zero-width joiner" (ZWJ) code in between (👨‍👨‍👧‍👧)
• All of the above, but with a dark-skin-tone modifier (\u0001F3FF) for 2 of the family members: \u0001F468\u0001F3FF\u200D\u0001F468\u200D\u0001F467\u200D\u0001F467\u0001F3FF (👨🏿‍👨‍👧‍👧🏿)

Note: I noticed that not all browsers have caught up on rendering that last example. In my browser it looks like this:

Conclusion: Emojis reflect society, and society adapts to emojis

As you might have noticed from that last sequence I shared, a single concept can call for many different emojis. As our society becomes more inclusive around gender, skin color, and differently capable people, emojis are keeping up. Everyone can express the concept in the way that is most meaningful for them. This is just one way that the language of emojis enriches our communication, and in turn our experience feeds back into the process and grows the emoji collection even more.

As emoji-rich data is used for reporting and for training of AI models, it's important for our understanding of emoji context and meaning to keep up with the times. Already we know that emoji use differs among different age generations and across other demographic groups. The use and application of emojis -- separate from the definition of emoji codes -- is yet another dimension to the data.

Our task as data scientists is to bring all of this intelligence and context into the process when we parse, interpret and build training data sets. The mechanics of parsing and producing emoji-rich data is just the start.

If you're encountering emojis in your data and considering them in your reporting and analytics, please let me know how! I'd love to hear from you in the comments.

The post How to work with emojis in SAS appeared first on The SAS Dummy.

People in my house are fans of the show, and they account for nearly 6,000 of those 57 billion streaming minutes. I can be this precise (nerd alert!) because I'm in the habit of analyzing our Netflix activity by using SAS. In fact, I can tell you that since late 2017, we've streamed 576 episodes of "The Office". We streamed 297 episodes in 2020. (Since the show has only 201 episodes we clearly we have a few repeats in there.)

I built a heatmap that shows the frequency and intensity of our streaming of this popular show. In this graph each row is a month, each square is a day. White squares are Office-free. A square with any red indicates at least one virtual visit with the Scranton crew; the darker the shade, the more episodes streamed during that day. You can see that Sept 15, 2020 was a particular big binge with 17 episodes. (Each episode is about 20-21 minutes, so it's definitely achievable.)

Heatmap of our household streaming of The Office

How to build the heatmap

To build this heatmap, I started with my Netflix viewing history (downloaded from my Netflix account as CSV files). I filtered to just "The Office (U.S.)" titles, and then merged with a complete "calendar" of dates between late 2017 and the start of 2021. Summarized and merged, the data looks something like this:

With all of the data summarized in this way such that there is only one observation per X and Y value, I can use the HEATMAPPARM statement in PROC SGPLOT to visualize it. (If I needed the procedure to summarize/bin the data for me, I would use the HEATMAP statement. Thanks to Rick Wicklin for this tip!)

proc sgplot data=ofc_viewing;
 title height=2.5 "The Office - a Netflix Journey";
 title2 height=2 "&episodes. episodes streamed on &days. days, over 3 years";
 label Episodes="Episodes per day";
 format monyear monyy7.;
 heatmapparm x=day y=monyear 
   colorresponse=episodes / x2axis
    outline
   colormodel=(white  CXfcae91 CXfb6a4a CXde2d26 CXa50f15) ;
 yaxis  minor reverse display=(nolabel) 
  values=(&allmon.)
  ;
 x2axis values=(1 to 31 by 1) 
   display=(nolabel)  ;
run;

You can see the full code -- with all of the data prep -- on my GitHub repository here. You may even run the code in your own SAS environment -- it will fetch my Netflix viewing data from another GitHub location where I've stashed it.

Distribution of Seasons (not "seasonal distribution")

If you examine the heatmap I produced, you can almost see our Office enthusiasm in three different bursts. These relate directly to our 3 children and the moments they discovered the show. First was early 2018 (middle child), then late 2019 (youngest child), then late 2020 (oldest child, now 22 years old, striving to catch up).

The Office ran for 9 seasons, and our kids have their favorite seasons and episodes -- hence the repeated viewings. I used PROC FREQ to show the distribution of episode views across the seasons:

Season 1 is remarkably low for two reasons. First and most importantly, it contains the fewest episodes. Second, many viewers agree that Season 1 is the "cringiest" content, and can be uncomfortable to watch. (This Reddit user leaned into the cringe with his data visualization of "that's what she said" jokes.)

From the data (and from listening to my kids), I know that Season 2 is a favorite. Of the 60 episodes we streamed at least 4 times, 19 of them were in Season 2.

More than streaming, it's an Office lifestyle

Office fandom goes beyond just watching the show. Our kids continue to embrace "The Office" in other mediums as well. We have t-shirts depicting the memes for "FALSE." and "Schrute Farms." We listen to The Office Ladies podcast, hosted by two stars of the show. In 2019 our daughter's Odyssey of the Mind team created a parody skit based on "The Office" (a weather-based office named Thunder Mifflin) -- and advanced to world finals.

Rarely does a day go by without some reference to an iconic phrase or life lesson that we gleaned from "The Office." We're grateful for the shared experience, and we'll miss our friends from the Dunder Mifflin Paper Company.

The post Visualizing our Netflix Trip through "The Office" appeared first on The SAS Dummy.

For a quick-start tutorial check out this 12-minute video on the SAS Users YouTube channel:

Many SAS products support direct integration with Git. This includes SAS Studio, SAS Enterprise Guide, and the SAS programming language. (That last one checks a lot of boxes for ways to use Git and SAS together.) While we have good documentation and videos to help you learn about Git and SAS, we often get questions around "best practices" -- what is the best/correct way to organize your SAS projects in Git?

In this article I'll dodge that question, but I'll still try to provide some helpful advice in the process.

Guidelines for managing SAS projects in Git

It’s difficult for us to prescribe exactly how to organize project repositories in source control. Your best approach will depend so much on the type of work, the company organization, and the culture of collaboration. But I can provide some guidance -- mainly things to do and things to avoid -- based on experience.

Do not create one huge repository

DO NOT build one huge repository that contains everything you currently maintain. Your work only grows over time and you'll come to regret/revisit the internal organization of a huge project. Once established, it can be tricky to change the folder structure and organization. If you later try to break a large project into smaller pieces, it can be difficult or impossible to maintain the integrity of source management benefits like file histories and differences.

Design with collaboration in mind

DO NOT organize projects based only on the teams that maintain them. And of course, don't organize projects based on individual team members.

• Good repo names: risk-adjustment-model, engagement-campaigns
• Bad repo names: joes-code, claims-dept

All teams reorganize over time, and you don't want to have to reorganize all of your code each time that happens. And code projects change hands, so keep the structure personnel-agnostic if you can. Major refactoring of code can introduce errors, and you don't want to risk that just because you got a new VP or someone changed departments.

Instead, DO organize projects based on function/work that the code accomplishes. Think modular...but don't make projects too granular (or you'll have a million projects). I personally maintain several SAS code projects. The one thing they have in common is that I'm the main contributor -- but I organize them into functional repos that theoretically (oh please oh please) someone else could step in to take over.

The Git view of my YouTube API project in SAS Enterprise Guide

Up with reuse, down with ownership

This might seem a bit communist, but collaboration works best when we don't regard code that we write as "our turf." DO NOT cling to notions of code "ownership." It makes sense for teams/subject-matter experts to have primary responsibility for a project, but systems like Git are designed to help with transparency and collaboration. Be open to another team member suggesting and merging (with review and approval) a change that improves things. GitHub, GitLab, and Bitbucket all support mechanisms for issue tracking and merge requests. These allow changes to be suggested, submitted, revised, and approved in an efficient, transparent way.

DO use source control to enable code reuse. Many teams have foundational "shared code" for standard operations, coded in SAS macros or shared statements. Consider placing these into their own project that other projects and teams can import. You can even use Git functions within SAS to fetch and include this code directly from your Git repository:

/* create a temp folder to hold the shared code */
options dlcreatedir;
%let repoPath = %sysfunc(getoption(WORK))/shared-code;
libname repo "&repoPath.";
libname repo clear;
 
/* Fetch latest code from Git */
data _null_;
 rc = git_clone( 
   "https://gitlab.mycompany.com/sas-projects/shared-code/",
   "&repoPath.");
run;
 
options source2;
/* run the code in this session */
%include "&repoPath./bootstrap-macros.sas";

If you rely on a repository for shared code and components, make sure that tests are in place so changes can be validated and will not break downstream systems. You can even automate tests with continuous integration tools like Jenkins.

DO document how projects relate to each other, dependencies, and prepare guidance for new team members to get started quickly. For most of us, we feel more accountable when we know that our code will be placed in central repositories visible to our peers. It may inspire cleaner code, more complete documentation, and a robust on-boarding process for new team members. Use the Markdown files (README.md and others) in a repository to keep your documentation close to the code.

My SAS code to check Pagespeed Insights, with documentation

Work with Git features (and not against them)

Once your project files are in a Git repository, you might need to change your way of working so that you aren't going against the grain of Git benefits.

DO NOT work on code changes in a shared directory with multiple team members –- you'll step on each other. The advantage of Git is that it's a distributed workflow and each developer can work with their own copy of the repository, and merge/accept changes from others at their own pace.

DO use Git branching to organize and isolate changes until you are ready to merge them with the main branch. It takes a little bit of learning and practice, but when you adopt a branching approach you'll find it much easier to manage -- it beats keeping multiple copies of your code with slightly different file and folder names to mark "works in progress."

DO consider learning and using Git tools such as Git Bash (command line), Git GUI, and a code IDE like VS Code. These don't replace the SAS-provided coding tools with their Git integration, but they can supplement your workflow and make it easier to manage content among several projects.

Learning more

When you're ready to learn more about working with Git and SAS, we have many webinars, videos, and documentation resources:

• Using SAS with Git: Bring a DevOps Mindset to Your SAS Code (Ask the Expert webinar)
• How do you manage your SAS projects with Git? (Ask the Expert webinar)
• Git functions in SAS 9.4 and SAS Viya (doc)
• Using Git in SAS Enterprise Guide (doc)
• Git with SAS Studio and SAS Enterprise Guide (video)
• Using built-in Git operations in SAS (blog)
• DevOps with SAS 9: SAS code, GitLab, and Jenkins (community)
• developer.sas.com for SAS app development
• Pro Git by Scott Chacon and Ben Straub, free online book about Git
• SAS Note about generating SSH key for use with Git and SAS

The post How to organize your SAS projects in Git appeared first on The SAS Dummy.

Each segment is called an octet because it contains 8 (count 'em, eight!) bits. The four-octect IP address is part of the IPv4 standard.

Note: There is a newer IPv6 standard (featuring 16 octets) that many newer networks use and which allows for more addresses. This has become necessary because all new consumer products are required by law to connect to the Internet. (I think that each of my daughter's "Polly Pocket" pieces can connect to WiFi.) But in this article I'm ignoring IPv6.

UPDATE 2020-Sep-04: My colleague Agata Bogacki has written a newer article that includes IPv6 and IPv4 and techniques to map to a geolocation.

The easy-to-read segmented IP address is actually a 32-bit number, and sometimes it is useful to convert the display value into its numeric form. For example, consider the databases that help you to map an IP address to a geographic location in the world. These databases use a numerical range to map an address to a country or city. (For more on range-based geocoding, see this topic in the PROC GEOCODE documentation.)

SAS code for converting IPv4 values to numbers

Here is a SAS program that converts a character-based, 4-segment IP address into its equivalent numeric value. It uses the SCAN function, a DATA step ARRAY, and a little bit of math to do the work:

/* Calculate the numerical IP from "segmented" IP address            */
/* Example: (from Right to Left)                                     */
/* 1.2.3.4 = 4 + (3 * 256) + (2 * 256 * 256) + (1 * 256 * 256 * 256) */
/*   is 4 + 768 + 13,1072 + 16,777,216 = 16,909,060                  */
data ip_numbers (keep=ip_address ip_numeric);
  infile datalines dsd;
  length ip_address $ 20 ip_numeric 8;
  input ip_address;
  array ip_part {4};
  do i = 1 to 4;
    ip_part{i} = scan(ip_address,i,'.');
  end;
  ip_numeric = ip_part{4} +
    (ip_part{3} * 256) +
    (ip_part{2} * 256 * 256) +
    (ip_part{1} * 256 * 256 * 256);
datalines;
115.85.65.148
117.203.114.198
118.96.201.156
119.247.220.11
12.201.116.58
128.2.38.96
128.204.197.27
128.204.207.83
134.102.237.2
141.155.113.98
169.2.124.79
172.16.26.231
172.16.30.229
173.234.211.69
176.63.76.232
178.157.198.132
178.32.145.44
178.32.177.184
178.33.174.213
178.63.199.204
184.82.208.149
188.165.187.71
;
run;

Here's the output:

Mapping IP address to a geo location

With this mapping, I can then combine my collection of IP addresses with one of the IP-to-geolocation databases that are available. (SAS provides a macro to help work with MaxMind, which you can learn about in this topic.) Here's a sample result:

The post Using SAS to convert IP addresses into numerical IP values appeared first on The SAS Dummy.

Update 25Aug2020: I've updated the graph and code in this post for the most recent versions of SAS Enterprise Guide and SAS. I also gave the chart a branding facelift, switching to the light-on-dark theme named "Raven". It's a personal preference influenced by today's trends -- but it's not economical on printer ink if you decide you want a hard copy.

You might notice that my chart is a bit busier than Rick's version. As a desktop client application that works across multiple SAS versions, we tend to issue major releases of SAS Enterprise Guide more often than we update the core engine of SAS software.

On my chart, the labels on the data points indicate the "event" that motivated the release. In some cases, we issued a new release to go with a new version of SAS (such as v1.2 with SAS 8.2). In other cases, we "remastered" a version to support more languages or another operating system (such as Microsoft Vista support with v4.1 in 2007, or the L10N release of v4.3 in 2010).

You might also notice that some of these releases happened very close together. Often, SAS R&D works on updates to multiple releases in parallel. Developers make changes to multiple versions to provide maintenance and hotfix changes (based on customer needs), while still contributing to the Next Big Version that will contain the compelling new features (based on customer wishes).

Here's the code that produced the chart:

data Releases;
  format Date DATE7.;
  input Category $8. Release $7. Date DATE9. Details $15.;
  datalines;
Ancient 1.0    01Nov1999 SAS 8.0
Ancient 1.1    01Jul2000 SAS 8.1
Ancient 1.2    01Apr2001 SAS 8.2
Ancient 1.3    01Sep2001 
Ancient 2.0    01Sep2002 
Ancient 2.05   01May2003 SAS 9.0
Ancient 2.1    01Apr2004 SAS 9.1
Ancient 3.0    01May2004 .NET rewrite
Ancient 3.02   01Feb2005 remaster/L10N
Ancient 4.1    01Mar2006 
Ancient 4.1v   01Apr2007 Vista
Ancient 4.2    01Mar2009 SAS 9.2
Ancient 4.22   01Sep2009 SAS 9.2m2
Old     4.3    01Aug2010
Old     4.305l 01Sep2010 L10N
Old     4.305r 01Jul2011 SAS 9.3
Old     5.1    01Feb2012
Old     6.1    01Jul2013 SAS 9.4
Old     7.1    01Oct2014 
Old     7.11   01May2015
Old     7.12   01Feb2016
Old     7.13   01Nov2016 SAS 9.4m4
Recent  7.15   01Sep2017 SAS 9.4m5
Recent  8.1    01Jun2019 Redesign
Recent  8.2    01Nov2019
Recent  8.3    18Aug2020 SAS 9.4m7
;
 
/* running in SAS EG - modify active ODS to add title to graph */
ods html5(id=eghtml) style=raven gtitle;
 
ods graphics / height=850 width=1200 ;
title font="AvenirNextforSAS Light" height=2.2 
  "SAS Enterprise Guide Releases (1999-2020)";
proc sgplot data=Releases noautolegend;
  styleattrs datacolors=(red yellow green);
  block x=date block=category / transparency = 0.75 
     valueattrs=(weight=bold size=14pt color=yellow 
      family='AvenirNextforSAS Light');
  scatter x=date y=release / datalabel=Details 
    datalabelpos=topleft dataskin=matte
    datalabelattrs=(size=10pt weight=bold)
    markerattrs=(symbol=CircleFilled size=14);
  xaxis grid type=time offsetmax=0.1 offsetmin=0 
        ranges=('01Jan1998'd-'01Jan2021'd) display=(nolabel) ;
  yaxis type=discrete grid offsetmax=0.1 
    label="Release/Event";
  inset "Updated &SYSDATE9." / 
     textattrs=(weight=bold) position=bottomright;
run;

The post Through the years: SAS Enterprise Guide versions appeared first on The SAS Dummy.

For those of us who use SAS to read and create Microsoft Excel documents, cloud-based files can add an extra wrinkle when we automate the process. It also adds some exciting possibilities! The Microsoft 365 suite offers APIs to discover, fetch, and update our documents using code. In this article, I'll show you how to use SAS programs to reach into your Microsoft OneDrive (or SharePoint Online) cloud to read and update your files. Note: All of this assumes that you already have a Microsoft 365 account -- perhaps provisioned by your IT support team -- and that you're using it to manage your documents.

Before I go on, I have to give major credit to Joseph Henry, the SAS developer who maintains PROC HTTP. Joseph did the heavy lifting for putting together the code and examples in this article. He also regularly adds new features to PROC HTTP that make it a more natural fit for calling REST APIs that require special authentication flows, such as OAuth2.

Note: I've updated this article several times to include detailed steps and "gotchas." I've added use cases for SharePoint Online and Microsoft Teams. I wrote a comprehensive paper for SAS Global Forum 2020. And I also recorded a 25-minute video (posted on SAS Support Communities) that shows all of the steps that I followed.

Click to watch the video tutorial.

Using SAS with Microsoft 365: an overview

Microsoft 365 uses an OAuth2-style authentication flow to grant access and permissions to third-party apps. If you're accustomed to the simpler style of just user/password authentication (ah, those were the days), OAuth2 can be intimidating. Joseph Henry does a great job of deconstructing OAuth2 -- with code samples -- in this SAS Global Forum paper.

When we're writing SAS programs to access Microsoft OneDrive or SharePoint, we're actually writing a third-party app. This requires several setup steps, a few of which cannot be automated. Fortunately, these need to be done just once, or at least infrequently. Here's an outline of the steps:

1. Register a new client application at the Microsoft Azure Portal. (You will need to sign in with your Microsoft 365 credentials, which might be your primary organization credentials if you have single-signon with Active Directory.)
2. Using your browser while you are signed into Microsoft 365, navigate to a special web address to obtain an authorization code for your application.
3. With your authorization code in hand, plug this into a SAS program (PROC HTTP step) to retrieve an OAuth2 access token (and a refresh token).
4. With the access token, you can now use PROC HTTP and the Microsoft 365 APIs to retrieve your OneDrive folders and files, download files, upload files, and replace files.

You'll have to complete Step 1 just once for your application or project. Steps 2 and 3 can be done just once, or at least just occasionally. The access token is valid for a limited time (usually 1 hour), but you can always exchange the refresh token for a new valid access token. This refresh token step can be automated in your program, usually run just once per session. Occasionally that refresh token can be revoked (and thus made invalid) when certain events occur (such as you changing your account password). When that happens, you'll need to repeat steps 2 and 3 to get a new set of access/refresh tokens.

Oh, and by the way, even though the examples in this article are specific to OneDrive, the exact same authentication flow and steps can be used for all of the Microsoft 365 APIs. Have fun with Outlook, Teams, Excel, and all of your favorite cloud-based Microsoft apps.

Step 1: Register your application

Visit the Microsoft Application Registration portal to register your new app. You'll sign in with your Microsoft 365 credentials.

Microsoft Application Registration portal

Click New Registration to get started. This presents you with a form where you can complete the details that define your app. Mainly, you're giving it a name and defining its scope. You'll probably want to limit its use to just your organization (your company) unless you're collaborating with colleagues who work elsewhere.

"Register an application" form

As you register your application, you also need to provide a redirect URL for the authorization flow. In our example, our app is considered "Public client/native (mobile/desktop)." The standard URL to indicate this is:

 https://login.microsoftonline.com/common/oauth2/nativeclient 

In the Redirect URI section, select this option and specify this URL value.

Redirect URI Selections

When you create an app, you'll receive a Client ID (unique to your app) and Tenant ID (unique to your organization). You'll need these values to obtain your authorization code and tokens later. The application portal provides a sort of control center for all aspects of your app. (Note: I masked out my client ID and tenant ID in this screenshot.)

Details for my sample application

Specifying your app permissions

Your app will need specific permissions in order to function. In my example, I want my SAS program to read documents from my OneDrive, and also add new docs and update existing docs. The permissions I need are:

• Files.ReadWrite.All: Allows the app to read, create, update and delete all OneDrive files that you can access.
• User.Read: Allows you to sign in to the app with your organizational account and let the app read your profile.
• Sites.ReadWrite.All (if using SharePoint): Allows the app to read, create, update and delete SharePoint Online files for sites that you can access.

To add these to your app, click the API Permissions tab in the control center. To be clear, these are not permissions that your app will automatically have. These are the permissions that will be requested when you "sign into" the app for the first time, and that you'll have to agree to in order for the app to run.

Adding permissions that the app needs

Permission types have their own terminology that is important to understand:

• Delegated versus Application Permissions: In our example, we are sticking to Delegated permissions, which allow the application to take actions on behalf of the signed-in user and provides access to the user's data. However, some use cases require use of Application permissions, which allow the application to take actions without a signed-in user and potentially access data across the system and different users.
• Admin Consent Required: Some permissions cannot be delegated or granted without the approval of an administrator. This restriction permits the organization to maintain oversight of the important resources that might be accessed by the application and to prevent unauthorized uses. The Microsoft Azure Portal provides an easy way for you to submit a request to an admin, so you can get the permissions that you need. However, I recommend that you follow up (or better yet, precede this) with a formal request to your IT support staff to state what you need and your business case. In my experience, this helps to expedite the process. A good working relationship with IT is important for any SAS user!

The documentation for the Microsoft Graph API provides a comprehensive list of the permission names, whether they are Delegated or Application level, and whether Admin Consent is required. This documentation also includes a helpful 4-minute video on the topic.

Possibly required: Obtaining admin consent

We're creating an app that hooks into your enterprise productivity suite -- and that's usually the domain of IT professionals. At SAS we are a tech company with many "citizen app developers", so our IT grants us more latitude than you might find at other places. But even at SAS, "normal" employees can't just create apps and empower them with access to our data. We have a process.

Because it's a common request, our IT folks created a form that makes it easy for them to review requests for new apps in our Microsoft 365 environment. The form asks:

• Your app name (“SAS via PROC HTTP” for mine)
• Your App (client) ID
• Grant type – my instructions assume "Authorization code grant type"
• Whether you need additional Delegated API permissions: Most need 'Files.ReadWrite.All' for OneDrive, 'Sites.ReadWrite.All' for SharePoint (in addition to the default 'User.Read').
• Whether your app needs Application Permissions. (Note: Answering YES here will trigger more scrutiny.)

Creating a configuration file

There are a few app-specific values that we'll need to reference throughout the SAS programs we're writing. I decided to create a configuration file for these settings rather than hard-code them into my SAS statements. This will make it easier for other people to reuse my code in their own applications.

I created a file named config.json that looks like this (but with different tenant_id and client_id values):

{
  "tenant_id": "206db638-6adb-41b9-b20c-95d8d04abcbe",
  "client_id": "8fb7804a-8dfd-40d8-bf5b-d02c2cbc56f3",
  "redirect_uri": "https://login.microsoftonline.com/common/oauth2/nativeclient",
  "resource" : "https://graph.microsoft.com"
}

By "externalizing" the IDs specific to my account/instance, I can use SAS code to read the values at run time. Note: This code, like all of the code in this article, uses features from SAS 9.4 Maintenance 5.

/*
  Set the variables that will be needed through the code
  We'll need these for authorization and also for runtime 
  use of the service.
 
  Reading these from a config.json file so that the values
  are easy to adapt for different users or projects.
*/
 
%if %symexist(config_root) %then %do;
  filename config "&config_root./config.json";
  libname config json fileref=config;
  data _null_;
   set config.root;
   call symputx('tenant_id',tenant_id,'G');
   call symputx('client_id',client_id,'G');
   call symputx('redirect_uri',redirect_uri,'G');
   call symputx('resource',resource,'G');
  run;
%end;
%else %do;
  %put ERROR: You must define the CONFIG_ROOT macro variable.; 
%end;

Step 2: Obtain an authorization code

Now that I've defined the application, it's time to "sign into it" and grant it the permission to read and manage content in OneDrive. This step needs to be completed from a web browser while I am signed into my Microsoft 365 account. The web address is very long...but we can use a SAS program to generate it for us.

/* location of my config file */
%let config_root=/folders/myfolders/onedrive;
 
%include "&config_root./onedrive_config.sas";
 
/* Run this line to build the authorization URL */
%let authorize_url=https://login.microsoftonline.com/&tenant_id./oauth2/authorize?client_id=&client_id.%nrstr(&response_type)=code%nrstr(&redirect_uri)=&redirect_uri.%nrstr(&resource)=&resource.;
options nosource;
%put Paste this URL into your web browser:;
%put -- START -------;
%put &authorize_url;
%put ---END ---------;
options source;

This produces these output lines in the SAS log:

 Paste this URL into your web browser:
 -- START -------
https://login.microsoftonline.com/206db638-6adb-41b9-b20c-95d8d04abcbe/oauth2/authorize?client_id=8fb7804a-8dfd-40d8-bf5b-d02c2cbc56
f3&response_type=code&redirect_uri=https://login.microsoftonline.com/common/oauth2/nativeclient&resource=https://graph.microsoft.com
 ---END ---------

Copy and paste the URL (all on one line, no spaces) into the address bar of your web browser. When you press Enter, you'll be prompted to grant the required permissions:

Once you click Accept, the browser will redirect to what looks like a blank page, but the URL contains the authorization code that we need:

Copy the value that appears after the code= in the URL, only up to the &session= part. It's going to be a very long string -- over 700 characters. We'll need that value for the next step.

Note: if you don't see the permissions prompt but instead see something like this:

App needs permission to access resources

Then you probably need to work with your IT support to grant consent for your app. See the section "Possibly required: Obtaining admin consent" above.

Step 3: Obtain an access token

My colleague Joseph wrote a few convenient utility macros that can help manage the access token and refresh token within your SAS session. These macros include:

• %get_token - get the initial access and refresh tokens, given an authorization code. Remember, an access token will expire in about 60 minutes. But the refresh token can be used to get a renewed access token.
• %refresh - exchange a valid refresh token for a new access token
• %process_token_file - read/update an external token file so that these values persist beyond your current SAS session.

I'm not going to walk through the macro code in this article, but the SAS programs are straightforward and well-documented. See "How to get this example code" at the end of this article.

With these macros in place, we can paste the (very long) authorization code we retrieved in the previous step into a macro variable. Then we can run the %get_token macro to generate the tokens and store them in a local file.

%let config_root=/folders/myfolders/onedrive;
 
%include "&config_root./onedrive_config.sas";
%include "&config_root./onedrive_macros.sas";
 
filename token "&config_root./token.json";
%let auth_code=AQABAAIAAAC5una0EUFgTIF8ElaxtWjTqwohjyfG; * and much more;
 
/*
  Now that we have an authorization code we can get the access token
  This step will write the tokens.json file that we can use in our
  production programs.
*/
%get_token(&client_id.,&auth_code,&resource.,token,tenant=&tenant_id);

Running this step will create a new file, token.json, in your designated config folder. Here's an screenshot of what my version looks like right now:

It's very important that you keep this file secure. With the information in this file (your refresh token) and your conf.json file (with your client ID and tenant ID), anyone can use these code techniques to impersonate you and access your Microsoft 365 data. There are techniques for storing these files such that only you can see them.

Using Microsoft 365 APIs to access OneDrive from SAS

Whew! I've spent nearly 1500 words to get this far, so thanks for sticking with me. The good news is that these steps take much longer to describe than to actually execute. Plus, creating apps is fun! (Right?)

From the screenshots I've shared, you probably already noticed that these services are working on Microsoft Azure, which is Microsoft's cloud platform for applications. For the remainder of this article, I'll be using methods from the Microsoft Graph API. This REST-based API provides access to almost all of Microsoft's hosted services. For my examples, I'll be using methods within the Files component of the API: Drives and Drive Items (folders and files).

You can explore and try the Microsoft 365 APIs with the Graph Explorer application from Microsoft. If you sign in with your own account, you can use the APIs with your own data. This is a great way to try these APIs and discover the correct methods to use before implementing them in your SAS code.

Example from a Graph Explorer session

Initializing and refreshing the access token in a new session

Now that we have the access and refresh tokens, we can get down to business with some actual OneDrive interactions. Here's how to initialize your SAS session with the tokens.

%let config_root=/folders/myfolders/onedrive;
 
%include "&config_root./onedrive_config.sas";
%include "&config_root./onedrive_macros.sas";
 
/*
  Our json file that contains the oauth token information
*/
filename token "&config_root./token.json";
 
%process_token_file(token);
 
/* If this is first use for the session, we'll likely need to refresh  */
/* the token.  This will also call process_token_file again and update */
/* our token.json file.                                                */
%refresh(&client_id.,&refresh_token.,&resource.,token,tenant=&tenant_id.);
 
/*
  At this point we have a valid access token and we can start using the API.
*/

If all goes well, we'll have our access token, and it will be stored in a macro variable named &access_token. It's going to be another long and illegible (>700 characters) value.

(Ever hear of the "infinite monkey theorem?" That a monkey hitting a typewriter for an infinite amount of time is certain to produce a certain text, such as the complete works of Shakespeare? Well, that monkey is not going to produce this access token. Plus, who has a typewriter anymore?)

Retrieving the top-level drive identifier (OneDrive)

We'll need to explore the OneDrive system from the top-down, using code. First, we need the identifier for the root drive. It's possible for you to have multiple root drives, and if that's the case for you, you'll need to modify this code a bit. This code queries the service for your drives, and stores the identifier for just the first drive in a macro variable. We'll need that identifier later to retrieve a list of top-level items.

/*
First we need the ID of the "drive" we are going to use.
to list the drives the current user has access to you can do this
*/
filename resp TEMP;
/* Note: oauth_bearer option added in 9.4M5 */
proc http url="https://graph.microsoft.com/v1.0/me/drives/"
     oauth_bearer="&access_token"
     out = resp;
	 run;
 
libname jresp json fileref=resp;
 
/*
 I only have access to 1 drive, but if you have multiple you can filter 
 the set with a where clause on the name value.
 
 This creates a data set with the one record for the drive.
*/
data drive;
 set jresp.value;
run;
 
/* store the ID value for the drive in a macro variable */
proc sql noprint;
 select id into: driveId from drive;
quit;

Note that this code uses the new OAUTH_BEARER option in PROC HTTP -- a convenient addition when working with OAuth2-compliant APIs. This is shorthand -- and more intuitive syntax -- for placing "Authorization: Bearer TOKEN-VALUE" in the HTTP headers.

Retrieving the top-level drive identifier (SharePoint Online)

The steps for SharePoint Online are nearly the same as for OneDrive, except that we need to reference the site hostname (yoursite.sharepoint.com, for example) and the /sites resource (instead of the /me/drives resource).

/* Note: oauth_bearer option added in 9.4M5                       */
/* Using the /sites methods in the Microsoft Graph API            */
/* May require the Sites.ReadWrite.All permission for your app    */
/* Set these values per your SharePoint Online site.
   Ex: https://yourcompany.sharepoint.com/sites/YourSite 
    breaks down to:
       yourcompany.sharepoint.com -> hostname
       /sites/YourSite -> sitepath
   This example uses the /drive method to access the files on the
   Sharepoint site -- works just like OneDrive.
   API also supports a /lists method for SharePoint lists.
   Use the Graph Explorer app to find the correct APIs for your purpose.
    https://developer.microsoft.com/en-us/graph/graph-explorer
*/
%let hostname = yourcompany.sharepoint.com;
%let sitepath = /sites/YourSite;
proc http url="https://graph.microsoft.com/v1.0/sites/&hostname.:&sitepath.:/drive"
     oauth_bearer="&access_token"
     out = resp;
	 run;
 
libname jresp json fileref=resp;
 
/*
 This creates a data set with the one record for the drive.
 Need this object to get the Drive ID
*/
data drive;
 set jresp.root;
run;
 
/* store the ID value for the drive in a macro variable */
proc sql noprint;
 select id into: driveId from drive;
quit;

Retrieve a list of top-level folders/files

With the drive identifier in hand (whether OneDrive or SharePoint), I can use the /children verb on the Microsoft Graph API to get a list of all of the top-level objects in that drive. These represent the folders and files that are at the root.

/*
 To list the items in the drive, use the /children verb with the drive ID
*/
filename resp TEMP;
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/root/children"
     oauth_bearer="&access_token"
     out = resp;
	 run;
 
libname jresp json fileref=resp;
 
/* Create a data set with the top-level paths/files in the drive */
data paths;
 set jresp.value;
run;

Here's what I'm keeping in my OneDrive right now. It's not too disorganized, is it?

List the files in a particular folder

If I'm interested in exploring a particular folder, I'll need to find the folder identifier as it's known to OneDrive. Using PROC SQL and SELECT INTO, I can find the folder by its name and store its ID in another macro variable. Then, I use the /children verb again, but this time with the folder ID instead of the "root" constant.

/*
 At this point, if you want to act on any of the items, you just replace "root" 
 with the ID of the item. So to list the items in the "SASGF" folder I have:
  - find the ID for that folder
  - list the items within by using the "/children" verb
*/
 
/* Find the ID of the folder I want */
proc sql noprint;
 select id into: folderId from paths
  where name="SASGF";
quit;
 
filename resp TEMP;
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&folderId./children"
     oauth_bearer="&access_token"
     out = resp;
	 run;
 
/* This creates a data set of the items in that folder, 
   which might include other folders.
*/
libname jresp json fileref=resp;
data folderItems;
 set jresp.value;
run;

Here are the items from my SASGF folder. Can you tell that I don't throw anything away?

Download a file from OneDrive and import into SAS

I know that I keep a spreadsheet named "sas_tech_talks_18.xlsx" in this SASGF folder. With the /content verb, I can download the file from OneDrive and store it in the file system that is local to my SAS session. Then, I can use PROC IMPORT to read it into a SAS data set.

/*
 With a list of the items in this folder, we can download
 any item of interest by using the /content verb 
*/
 
/* Find the item with a certain name */
proc sql noprint;
 select id into: fileId from folderItems
  where name="sas_tech_talks_18.xlsx";
quit;
 
filename fileout "&config_root./sas_tech_talks_18.xlsx";
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&fileId./content"
     oauth_bearer="&access_token"
     out = fileout;
	 run;
 
/* Import the first sheet into a SAS data set */
proc import file=fileout 
 out=sasgf
 dbms=xlsx replace;
run;

Boom! I've just downloaded my data from the cloud and brought it into my SAS session.

Add a new file to OneDrive

We can build wonderful documents from SAS too, and it's important to be able to share those. By using the PUT method with the /content verb, we can copy a file from the local SAS session into a target folder on OneDrive. Most often, this will probably be an Excel spreadsheet or maybe a PDF report. (But hey, maybe it's a good opportunity to try out the new ODS WORD destination in SAS 9.4 Maint 6?)

/*
  We can upload a new file to that same folder with the PUT method and /content verb
  Notice the : after the folderId and the target filename
*/
 
/* Create a simple Excel file to upload */
%let targetFile=iris.xlsx;
filename tosave "%sysfunc(getoption(WORK))/&targetFile.";
ods excel(id=upload) file=tosave;
proc print data=sashelp.iris;
run;
ods excel(id=upload) close;
 
filename details temp;
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&folderId.:/&targetFile.:/content"
  method="PUT"
  in=tosave
  out=details
  oauth_bearer="&access_token";
run;
 
/*
  This returns a json response that describes the item uploaded.
  This step pulls out the main file attributes from that response.
*/
libname attrs json fileref=details;
data newfileDetails (keep=filename createdDate modifiedDate filesize);
 length filename $ 100 createdDate 8 modifiedDate 8 filesize 8;
 set attrs.root;
 filename = name;
 modifiedDate = input(lastModifiedDateTime,anydtdtm.);
 createdDate  = input(createdDateTime,anydtdtm.);
 format createdDate datetime20. modifiedDate datetime20.;
 filesize = size;
run;

Replace/update a file in OneDrive

If you want to replace an existing file, then you'll want to perform the additional step of retrieving the unique ID for that file from OneDrive. When you PUT the new version of the file into place, its history and sharing properties should remain intact. Here is my code for navigating the folder/file structure in my OneDrive and finally replacing an existing file.

/*
  If you want to replace a file instead of making a new file 
  then you need to upload it with the existing file ID.  If you
  don't replace it with the existing ID, some sharing properties
  and history could be lost.
*/
/* Create a simple Excel file to upload */
%let targetFile=iris.xlsx;
filename tosave "%sysfunc(getoption(WORK))/&targetFile.";
ods excel(id=upload) file=tosave;
proc print data=sashelp.iris;
run;
ods excel(id=upload) close;
 
/* Navigate the folder and file IDs from my OneDrive */
proc sql noprint;
 select id into: folderId from paths
  where name="SASGF";
quit;
 
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&folderId./children"
     oauth_bearer="&access_token"
     out = resp;
	 run;
 
libname jresp json fileref=resp;
data folderItems;
 set jresp.value;
run;
 
/* Find the ID of the existing file */
proc sql noprint;
 select id into: fileId from folderItems
  where name="iris.xlsx";
quit;
 
libname attrs json fileref=details;
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&fileId./content"
 method="PUT"
 in=tosave
 out=details
 oauth_bearer="&access_token";
run;

As you can see from my OneDrive history for this file, I've tested this program a few times -- resulting in 23 revisions of this file in its history!

How to get this example code

You can find the source files for these examples on GitHub.

I've organized this code into 5 different files in order to make it easy to reuse:

• onedrive_config.sas - read the fields from the conf.json and set them as global macro variables. This includes your client_id and tenant_id.
• onedrive_setup.sas - the SAS statements that represent code you will need to run just once to get your authorization code and first access code.
• onedrive_macros.sas - three utility macros that help you to create, refresh, and manage your access token and refresh token in your token.json file
• onedrive_example_use.sas - sample SAS steps that I used in this article. They won't quite work for you as-is, since you don't have the same files that I do. (Unless you do have the same files, in which case...creepy.) My hope is that you can read and adapt them for your own content.
• onedrive_sharepoint_example.sas - sample SAS steps for reading and writing files with SharePoint Online. The basic steps are the same as for OneDrive, except that you use the /sites resource instead of the OneDrive-specific methods.

I also included a template for the conf.json file, with obvious placeholders for the client_id and tenant_id that you'll substitute with your own values. You'll also need to change the statements that define &CONFIG_LOC -- the location of your configuration directory where you're storing these files. I developed these examples in SAS University Edition -- yes, this works there! I also ran the code from my full SAS environment via SAS Enterprise Guide.

More about using REST APIs from SAS

This has been a monster article -- in terms of its length. But I hope it's clear enough to follow and has sufficient detail for you to try this on your own. If you have questions, post in the comments.

I've published a number of other articles about using REST APIs from SAS -- it's one of my favorite things to do. Check out:

• Using Google Analytics APIs from SAS
• Using GitHub APIs from SAS
• How to publish to a Microsoft Teams channel using SAS
• Sending messages to a Slack channel with SAS
• The Internet of Snacks: SnackBot data and what it reveals about SAS life
• How to test PROC HTTP and the JSON library engine
• Securing your REST API credentials in your SAS programs

The post Using SAS with Microsoft 365 (OneDrive, Teams, and SharePoint) appeared first on The SAS Dummy.

The SAS Enterprise Guide application runs here, on your desktop. The SAS Workspace session (which accesses data and cranks through your analysis) runs over there, on a remote machine. If you're accustomed to "PC SAS" running all on your local box, you might be forced to rethink a few of your processes. For example, do you have Excel files on your PC to import using PROC IMPORT? Does your SAS program create output that you have to save on your PC? How will you get these files to where they need to be?

There are some strategies that help, including mapped drives, UNC paths, and network shares via a mechanism like Samba. These mechanisms allow your local SAS Enterprise Guide and your remote SAS to "see" a shared location on the network. Also, SAS Enterprise Guide has a few focused tasks that can "move" files for you under the guise of "import" and "export" steps.

But there isn't a general method to copy any file you want from your PC to the SAS Workspace, or from the SAS Workspace to your PC. Until now.

I give you: the Copy Files task for SAS Enterprise Guide.

Note: This article was originally published in 2012. It's been updated periodically with new information. Most recently, I added a link to a video tutorial about the Copy Files task.

What "Copy Files" does

The Copy Files task allows you to capture file transfer steps within your SAS Enterprise Guide process flow. You can upload files from your local PC to the remote machine where your SAS Workspace is running. And you can download files from your remote SAS Workspace to a folder on your local PC. It is similar in concept to an FTP process, except that this transfer operation uses your already-existing connection to a SAS Workspace and does not require a separate application.

Some readers might be familiar with SAS/CONNECT Data Transfer Services (PROC UPLOAD and PROC DOWNLOAD), which allows you to transfer files between two SAS sessions. Again, this is similar in concept, but since there is only one SAS session in play here, we need to use a different mechanism.

How to copy files in bulk

The Copy Files task supports two features that allow you to copy multiple files with a single step. First, you can use wildcard characters to match on multiple file names. An asterisk (*) matches all characters, in any number, before the next non-wildcard character. A question mark (?) matches any single character that occupies that position in the file name.

Second, you can use SAS macro variables and expressions to specify any part of the source file names or destination folder. Use a SAS program to determine the files you need to copy earlier in the process, assign that value to a macro variable, and then have the task "dynamically" select the correct files when it's time to copy them.

Support for Task Templates

The Copy Files task also supports a standard SAS Enterprise Guide feature: Task Templates. You can create an instance of the task that is useful in one process, then save those task settings to your personalized set of Task Templates that you can use in another process or even in another project. To save your settings as a template, right-click on the Copy Files task within your process flow and select Create Task Template. To use the new template, select it as a menu item from Tasks->Task Templates.

To see the Copy Files task in action, watch this 11-minute portion of my SAS Enterprise Guide tutorial.

Limitations to note

This task does not generate a SAS program that you can reuse in a batch SAS process or SAS stored process. The task works by using SAS Workspace APIs to transfer data across your network connection, so all of the work happens "behind the scenes". However, the task does create a detailed log output that shows what files were copied, how many bytes were transferred, how long it took, and whether there were any errors.

How to access the Copy Files task

You'll find the Copy Files task in the Data category of the SAS Tasks list. Here's the view from SAS Enterprise Guide v8.2:

This task has been included in SAS Enterprise Guide since the v7.13 release (late 2017). Are you using an older version? You really should consider upgrading! But, with a little work, you can add the task to older versions.

You can download the Copy Files task from this link (Zip file). The download package includes multiple versions that depend on your version of SAS Enterprise Guide: v4.3, v5.1, v6.1, v7.1 (before v7.13), and one for v4.1 (with a reduced feature set). There is also a documentation file (PDF) that provides instruction for how to install and use the task. You might need to perform this extra step to "unblock" the downloaded DLL to avoid an error ("HRESULT: 0x80131515") when you try to add the task to your installation.

The post How to copy files in SAS Enterprise Guide appeared first on The SAS Dummy.

If you're among the hundreds of people who had planned to present (as I am), you might feel a sense of great loss. After all, you already did most of the work: proposed a topic, earned an acceptance, wrote a paper (!), and maybe even completed your presentation materials. (Did you already rehearse for your colleagues or in front of a mirror?)

In this article, I'll share some ideas that you can use to fill the gap, and to extend the reach of your SAS knowledge beyond just those who would have attended the conference. Specifically, I'll address how you can make the biggest splash and have an enduring impact with that traditional mode of SAS-knowledge sharing: the SAS conference paper.

Extending the reach of your SAS Global Forum paper

Like many of you, I've written and presented a few technical papers for SAS Global Forum (and also for its predecessor, SUGI). With each conference, SAS publishes a set of proceedings that provide perpetual access to the PDF version of my papers. If you know what you're looking for, you can find my papers in several ways:

• On the dedicated proceedings page for each conference year. For example, here's the collection from SAS Global Forum 2020.
• By searching the SAS support site, using the proper keywords that relate to my topics.
• By searching lexjansen.com -- a labor-of-love website by Lex Jansen, who indexes and makes it easy to find virtually all SAS-related conference papers.

All of these methods work with no additional effort from me. When your paper is published as part of a SAS conference, that content is automatically archived and findable within these conference assets. But for as far as this goes, there is opportunity to do so much more.

Write an article for SAS Support Communities

ArtC's presenter page

Back in the day, sasCommunity.org supported the idea of "presenter pages" -- a mini-destination for information about your conference paper. As an author, you would create a page that contains the description of your paper, links to supporting code, and any other details that you wanted to lift out of the PDF version of your paper. Creating such a page required a bit of learning time with the wiki syntax, and just a small subset of paper presenters ever took the time to complete this step. (But some prolific contributors, such as Art Carpenter or Don Henderson, shared blurbs about dozens of their papers in this way.) Personally, I created a few pages on sasCommunity.org to support my own papers over the years.

SAS Support Communities offers a similar mechanism: the SAS Communities Library. Any community member can create an article to share his or her insights about a SAS related topic. A conference paper is a great opportunity to add to the SAS Communities Library and bring some more attention to your work. A communities article also serves as platform for readers to ask you questions about your work, as the library supports a commenting feature that allows for discussion.

I created articles on SAS Support Communities to address some of my previous papers. I also updated the content, where appropriate, to ensure that my examples work for modern releases of SAS. Here are two examples of presentation pages that I created on SAS Support Communities:

• Doing more with SAS Enterprise Guide automation (supporting a paper from 2012)
• Create your own client apps with SAS Integration Technologies (supporting a paper from 2013)

One of my presentations on in the SAS Communities Library

When you publish a topic in the SAS Communities Library, especially if it's a topic that people search for, your article will get an automatic boost in visitors thanks to the great search engine traffic that drives the communities site. With that in mind, use these guidelines when publishing:

• Use relevant key words/phrases in your article title. Cute and clever titles are a fun tradition in SAS conference papers, and you should definitely keep those intact within the body of your article. But reserve the title field for a more practical description of the content you're sharing.
• Include an image or two. Does your paper include an architecture diagram? A screen shot? A graph or plot? Use the Insert Photos button to add these to your article for visual interest and to give the reader a better idea of what's in your paper.
• Add a snippet of code. You don't have to attach all of your sample code with hundreds of program lines, but a little bit of code can help the reader with some context. Got lots of code? We'll cover that in the next section.

More examples to inspire you

This year (2020), the industry conference PharmaSUG was also cancelled due to COVID-19. But that didn't stop Jeffrey Meyers, a prolific PharmaSUG contributor, from sharing his work! He published his presentations in article form on the SAS Communities Library, and attached code and his formal paper as a PDF. (For SAS Global Forum, the papers are published in the proceedings -- so if you follow Jeff's example you should not attach your paper. Rather, just link to it from your article.)

• %MVMODELS: a Macro for Survival and Logistic Analysis
• Data Library Comparison Macro %COMPARE_ALL
• Demographic Table and Subgroup Summary Macro %TABLEN

To get started with the process for creating an article...see this article!

Share your code on GitHub

SAS program code is an important feature in SAS conference papers. A code snippet in a PDF-style paper can help to illustrate your points, but you cannot effectively share entire programs or code libraries within this format. Code that is locked up in a PDF document is difficult for a reader to lift and reuse. It's also impossible to revise after the paper is published.

GitHub is a free service that supports sharing and collaboration for any code-based technology, including SAS. Anyone who works with code -- data scientists, programmers, application developers -- is familiar with GitHub at least as a reader. If you haven't done so already, it might be time to create your own GitHub account and share your useful SAS code. I have several GitHub repositories (or "repos" as we GitHub hipsters say) that are related to papers, blog posts, and books that I've written. It just feels like a natural way to share code. Occasionally a reader suggests an improvement or finds a bug, and I can change the code immediately. (Alas, I cannot go back in time and change a published paper...)

We invite you to contribute your code and other materials to the SAS Global Forum GitHub repository. We organized this repo as a central location for all presenters to share code that helps other SAS professionals to use the tips you've shared in your paper.

A sample of conference-paper-code on my GitHub.

Add a video of your presentation

Many SAS Global Forum presenters have been invited and agreed to record a short video version of their presentation. These will be available on the SAS Users channel on YouTube. If you write an article for the SAS Community, you can also embed your video from YouTube to complete the package.

If you don't have a YouTube version, you're still invited to contribute a video! On the SAS Community you can upload your own video to include in your article. Record these using Camtasia or OBS (Open Broadcaster Software) or whatever tech you're comfortable with. You can then add the MP4 or MOV file via the Add Video tool while editing your SAS Communities Library article. Here's an example of a video within the community: Viewing SAS catalog entries and formats within SAS Enterprise Guide.

List your published work on your LinkedIn profile

So, you've prepared/presented your work at a major SAS conference! Your professional network needs to know this about you. You should list this as an accomplishment on your resume, and definitely on your LinkedIn profile.

LinkedIn offers a "publication" section -- perfect for listing books and papers that you've written. Or, you can add this to the "projects" section of your profile, especially if you collaborate with someone else that you want to include in this accomplishment. I have yet to add my entire back-catalog of conference papers, but I have added a few recent papers to my LinkedIn profile.

One of a few publications listed on my LinkedIn profile

Bonus step: write about your experience in a LinkedIn article

Introspection has a special sort of currency on LinkedIn that doesn't always translate well to other places. A LinkedIn article -- a long-form post that you write from a first-person perspective -- gives you a chance to talk about the deeper meaning of your project. This can include the story of inspiration behind your conference paper, personal lessons that you learned along the way, and the impact that the project had in your workplace and on your career. This "color commentary" adds depth to how others see your work and experience, which helps them to learn more about you and what drives you.

Here are a few examples of what I'm talking about:

• Susan Slaughter (coauthor of The Little SAS Book) talks about her paper/presentation about SGPLOT.
• Here's a LinkedIn article that I wrote about a SAS Global Forum video series that I host -- a sort of "behind the scenes" story.
• And you should connect with/follow our favorite "SAS Nerd" Kirk Lafler. He's a master at sharing his SAS content on LinkedIn.

It's not about you. It's about us.

The techniques I've shared here might sound like "how to promote yourself." Of course, that's important -- we each need to take responsibility for our own self-promotion and ensure that our professional achievements shine through. But more importantly, these steps play a big role in helping your content to be findable -- even "stumble-uponable" (a word I've just invented). You've already invested a tremendous amount of work into researching your topic and crafting a paper and presentation -- take it the extra bit of distance to make sure that the rest of us can't miss it.

(Author's note: I originally published this article in 2017, when sasCommunity.org was "retired" and SAS professionals were looking for an outlet to share their work. I've adapted the guidance here for our "virtual conference" reality in 2020.)

The post How to share your SAS knowledge with your professional network appeared first on The SAS Dummy.

(Update 23Mar2020) I've recorded a video that shows how to use the Import Data task as a start, and then adapt the code that it generates for reuse in other environments, with other files, and to read multiple text files in a single step. Watch the video to learn more, or scroll down to read my step-by-step tips.

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:

• Using the Import Data task in SAS Enterprise Guide. This one shows how to import Microsoft Excel files, but the basic steps are similar in the interface.
• Read CSV files with SAS Studio
• Reading raw data with the INPUT statement. (Documentation topic)

The post Tricks for importing text files in SAS Enterprise Guide appeared first on The SAS Dummy.

SAS Macro Variable viewer: features and function

This tool offers the following main features:

Always-visible window: Once you open the task from the Tools menu, you can leave it open for your entire SAS Enterprise Guide session. The window uses a "modeless" display, so you can still interact with other SAS Enterprise Guide features while the window is visible. This makes it easy to switch between SAS programs and other SAS Enterprise Guide windows and the macro variable viewer to see results.

Select active SAS server: If your SAS environment contains multiple SAS workspace connections, you can switch among the different servers to see macro values on multiple systems.

One-click refresh: Refresh the list of macro variables by clicking on the Refresh button in the toolbar.

View by scope or as a straight list: View the macro variables in their scope categories (for example, Global and Automatic) or as a straight list, sorted by variable name or current value. Click on the column headers to sort the list.

Filter results: Type a string of characters in the "Filter results" field, and the list of macro variable results will be instantly filtered to those that contain the sequence that you type. The filtered results will match on variable names as well as values, and the search is case-insensitive. This is a convenient way to narrow the list to just a few variables that you're interested in. To clear the filter, click on the X button next to the "Filter results" field, or "blank out" the text field.

Set window transparency: You can make the window appear "see-through" so that it doesn't completely obscure your other windows as you work with it.

Copy macro variables as %LET statements: Select one or more macro variables within the window, right-click and select Copy assignments. This generates a series of %LET statements -- one for each macro variable/value pair -- which you can then paste into a SAS program.

Macro expression "quick view": Have you ever wanted to test out a macro expression before using it in a longer program? This window allows you to get immediate feedback on a macro expression, whether a simple macro reference or a more complex expression with nested functions. If the expression generates a SAS warning or error, the feedback window shows that as well. Note: the expression can be any macro expression that is valid for the right-side of a macro variable assignment (%let statement).

Source code available on GitHub

I created this tool years ago as a custom task, but it's been included with SAS Enterprise Guide as an "official" tool for several releases. I built the task using the custom task APIs and Microsoft .NET. If you're interested, I've shared a version of the source code behind this task on GitHub.

The post Viewing SAS macro variables in SAS Enterprise Guide appeared first on The SAS Dummy.