topic Re: Python-like Dictionaries in SAS? in SAS Programming

Python-like Dictionaries in SAS?

mark4 — Fri, 25 May 2018 17:36:16 GMT

Does SAS have something similar to Python's dictionary? For those unfamiliar, an easy way to think about it is a data set with two columns - name and value. For example, the 'name' column could be a person's name, and the 'value' column could be their address. One great feature about this is the ability to quickly 'look-up' the value for a given name. If the dictionary was called dict, then to get John Doe's address, you evaluate the expression dict['John Doe'].

You could get the same result with something like

proc sql noprint;

select address into :Johns_Address from dict where name = "John Doe";

quit;

so calling &Johns_Address will return John Doe's address. This is obviously clunky and less than ideal. Are there shortcuts?

Re: Python-like Dictionaries in SAS?

Reeza — Fri, 25 May 2018 17:44:25 GMT

SAS Formats function like that and you can build them off a data set using CNTLIN. Then you use it, using a PUT or INPUT statement depending on the conversion direction.

Re: Python-like Dictionaries in SAS?

mark4 — Fri, 25 May 2018 17:47:30 GMT

Thanks. Can you explain a little more about what you mean and how it works? I only know SAS Formats as a rather frustrating thing I have to deal with using date/time or converting between character and numeric, and I've never heard of CNTLIN.

Re: Python-like Dictionaries in SAS?

Kurt_Bremser — Fri, 25 May 2018 17:57:04 GMT

proc format does not only work from code, it can also create a format from a dataset (it's the cntlin= option in the proc format statement).

To see how such a dataset should look like, create one with the cntlout= option.

For a typical "lookup" format, you need just 4 variables:

start, the "from" value
label, the "to" value
type, either 'C' or 'N'
fmtname, the name of the format

Re: Python-like Dictionaries in SAS?

Reeza — Fri, 25 May 2018 17:57:28 GMT

Here’s a good paper with examples and code:

http://www2.sas.com/proceedings/sugi30/001-30.pdf

And some of the technical concepts:

https://support.sas.com/resources/papers/proceedings12/048-2012.pdf

Your current references are built in SAS formats, but you can also create your own custom formats, User Defined Formats (UDF).

It’s basically a lookup table and faster than joins.

Re: Python-like Dictionaries in SAS?

ballardw — Fri, 25 May 2018 20:07:42 GMT

@mark4 wrote:

Thanks. Can you explain a little more about what you mean and how it works? I only know SAS Formats as a rather frustrating thing I have to deal with using date/time or converting between character and numeric, and I've never heard of CNTLIN.

Converting between may mean that you have a confusion between date, time and datetime FORMATS, which are used to display SAS numeric values in human readable form and INFORMATS which are used to translate human readable dates, times and datetimes into computer useable numeric forms.

Proc format allows you to create custom display formats or informats to read values into a variable.

CNTLIN is an option for Proc Format that will use specific variable names and values to create a format. CNTLOUT is an option to create a data set from Proc format code that can be used as a CNTLIN dataset.

SAS does have dictionary tables but they are different than you may be familiar with. The SAS dictionary tables keep track of SAS items such as library characteristics (path associated data engine and such), table characteristics (name, number of variables, number of records, library membership, variable names and characteristics and which data sets they belong to, defined macros, titles, footnotes and other system items.

Re: Python-like Dictionaries in SAS?

Ksharp — Sat, 26 May 2018 10:47:32 GMT

Hash Table/Map .

Re: Python-like Dictionaries in SAS?

mark4 — Tue, 29 May 2018 12:24:09 GMT

Thanks. I will certainly start using hashes. The syntax and implementation is much cleaner, shorter, and easier to remember than using PROC FORMAT.

Quickly browsing the available resources implies you can only use hashes in a data set, and once you're out of the data step, they are no longer available, though. Are there any work-around's to this? For just one example, I would love to create something like this for all data sets: Each data set corresponds to a key, and the value from that key is an array consisting of various attributes for the data set like number of rows, number of columns, column names, etc. Having this with the ability to print a key's value to the log would make testing/debugging/exploring new data sets much more efficient. I wouldn't have to get information on data sets of interest by hunting around library views to click on the data set, and I wouldn't have to sift through far too much information printed to the Results tab when you use PROC CONTENTS.

Re: Python-like Dictionaries in SAS?

mark4 — Tue, 29 May 2018 12:31:29 GMT

I'll have to look at the ctlin/ctlout options and see what they do. (I don't even know what they're abbreviating.) But a lot of my confusion comes from importing external data where dates are not formatted consistently. At times they are text, sometimes they are displayed YYYYMMDD, other times YYYYJJJ where JJJ is the Julian date. I usually just turn everything into numeric values and work from there. Even this is not without problems since I cannot remember the difference in input vs put for converting between formats and the other 'format' syntax requirements (do I need a $ or a ., do I need some sort of length?).

Re: Python-like Dictionaries in SAS?

Ksharp — Tue, 29 May 2018 12:35:15 GMT

Yeah. That is the benefit of FORMAT , which most people suggested.

Re: Python-like Dictionaries in SAS?

mark4 — Tue, 29 May 2018 12:45:14 GMT

Thanks. That's too bad you can't access hashes outside data steps. It's such an inelegant solution. Yet another reason having to segment SAS programs into all sorts of proc's, data sets, macros is so cumbersome...

Re: Python-like Dictionaries in SAS?

ballardw — Tue, 29 May 2018 16:42:13 GMT

@mark4 wrote:

I'll have to look at the ctlin/ctlout options and see what they do. (I don't even know what they're abbreviating.) But a lot of my confusion comes from importing external data where dates are not formatted consistently. At times they are text, sometimes they are displayed YYYYMMDD, other times YYYYJJJ where JJJ is the Julian date. I usually just turn everything into numeric values and work from there. Even this is not without problems since I cannot remember the difference in input vs put for converting between formats and the other 'format' syntax requirements (do I need a $ or a ., do I need some sort of length?).

Without seeing concrete examples I would guess that you are relying on Proc Import too much. Since Proc Import has to guess on variable characteristics based on a default very small number of records in a file imported this in not an uncommon issue, especially if you deal with marginally organized data such as from spreadsheets.

SAS will usually default to assigning a display format to match the format the data was read from. In which case all that would be needed is to assign you preferred date format after the data is read (Proc datasets would modify the data set and variable properties like Format or label in place).

Re: Python-like Dictionaries in SAS?

Reeza — Tue, 29 May 2018 17:23:34 GMT

You're mixing up two concepts here, one is how SAS stores variables and formats them, and the second is how to import data correctly.

Someone's already mentioned the import issue.

Regarding variables and formats, Formats is actually one of the most powerful features of SAS in the long run. The presentation layer is separated from the data layer (which makes it a bit more abstract for the day to day users) but makes it much easier to manage data. I've started becoming more and more reliant on them after a decade of using SAS because they're versatile and quick.

For dates, they are numbers, you can subtract, add, increment them as necessary. The formats simply change how they're displayed. Its especially nice when I get data sets from American sources that store data in a mmddyyyy format I can change that right away to what the rest of the world uses.

Here's a tutorial on using dates and times in SAS:

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354

The issues you're encountering are issues I face in all languages (Python and R included) - most import automatic procedures have issues and dates are stored as numbers in most languages, even Excel. The formats is a SAS specific issue.

Re: Python-like Dictionaries in SAS?

mark4 — Tue, 29 May 2018 18:05:48 GMT

Thanks. I think I have an understanding of how formats work, but I have never thought about it in depth so I may be wrong. For a variable that's declared a date, SAS stores it as a numeric value representing the amount of time between your date and some arbitrary point in time. Then declaring the format simply dictates how the variable is displayed, but it does not change the value stored in the data set, right?.

I agree that dates are annoying no matter what you're using, and my annoyance isn't mixing up formatting and importing so much as just trying to manage inconsistent fields. The trouble for me comes from having to remember the syntax for the various date functions (take Julian date to YYYYMMDD, vs take YYMMDD to YYYYMMDD, for example) in SAS and thinking about how that interacts with formats. I receive sas files that contain fields that represent dates, but some are stored as dates, dates, some as character, others as numeric, and some of these will later have to be exported to excel using the same display format. That's why I find it easiest to just convert it all a fixed 'format' but as a character, and if I need to do some arithmetic, treat it as numeric for the moment.

Re: Python-like Dictionaries in SAS?

mark4 — Tue, 29 May 2018 18:11:57 GMT

As I mention below, I receive a lot of data from SAS files created by others, and the issue is the inconsistency in how those dates are formatted. I'll have to start using PROC DATASETS for these before reading them in, it appears. But for those cases where I do have to import from csv or excel, I don't understand how PROC DATASETS helps, though.

Is there not just a way to declare the variable types in a PROC IMPORT section, or the first time I go to use the data? I find the large number of procedures you have to know to use SAS efficiently to be a hindrance. I don't like cases where you have to break what should be a single step into multiple SAS steps (e.g., importing data according to user defined criteria like column names or variable types) - each step has it's own syntax and structure, and it makes it hard to keep track of how each works.

Re: Python-like Dictionaries in SAS?

Reeza — Tue, 29 May 2018 20:14:27 GMT

That’s a data integration problem in your process, not a SAS issue per se. You’d have the same issues in any other system, including Python AFAIK.

Re: Python-like Dictionaries in SAS?

SASKiwi — Tue, 29 May 2018 21:10:08 GMT

I think you need to stop comparing SAS programs with the way other languages have implemented stuff - every language has its idiosyncracies. There are good reasons why SAS is structured the way it is and as your knowledge grows you will see many areas where SAS goes way beyond what you can do in other languages. To give you an example, using SAS formats as lookups is incredibly powerful. I've built DATA steps that do in excess of 100 SAS format lookups - try doing that in SQL table joins or hash lookups.

Re: Python-like Dictionaries in SAS?

Tom — Tue, 29 May 2018 21:38:37 GMT

@mark4 wrote:

Does SAS have something similar to Python's dictionary? For those unfamiliar, an easy way to think about it is a data set with two columns - name and value. For example, the 'name' column could be a person's name, and the 'value' column could be their address. One great feature about this is the ability to quickly 'look-up' the value for a given name. If the dictionary was called dict, then to get John Doe's address, you evaluate the expression dict['John Doe'].

...

Yes.

They are called formats. Let's take your example of translating a name to an address. For that you would create a character format (since name is character value).

proc format ;
  value $address
    'John Doe'='101 Main Street'
  ;
run;

You could then use the format to display the addresses. For example to get frequency counts by address you could just do:

proc freq data=users ;
  tables name ;
  format name $address.;
run;

If you want to create a new variable that has the formatted value then use the PUT function.

data new ;
  set old;
  address=put(name,$address.);
run;

If you already have the data for your format in a dataset then you can either use the data to generate the VALUE statement of PROC FORMAT.

filename code temp;
data _null_;
  set name_address_list end=eof;
  file code ;
  if _n_=1 then put 'value $address';
  put name :$quote. '=' address :$quote. ;
  if eof then put ';';
run;
proc format;
%include code;
run;

Or convert the data into the data format that PROC FORMAT can use for generating formats.

data fmtin ;
  set name_address_list (keep=name address) ;
  fmtname='$address';
  rename name=start address=label;
run;
proc format cntlin=fmtin; 
run;