topic Re: How to revisit observations in DS2 in SAS Programming

How to revisit observations in DS2

JacobSimonsen — Fri, 17 Oct 2014 11:53:02 GMT

Hello everyone,

I wonder whether it is possible to revisit observations with DS2. The condition is that I don't know beforehand how many times I will revisit an observation. This can be the case if for instance I use a newton raphson procedure to maximize a function, and I dont know how many iterations I need to get convergence. For each itereation I need to go through all observations. The reason why I want to use DS2 is to get access to the matrix package.

In the ordinary datastep I know of two ways to get random access to observations: The open() function and the point= option. Simple examples of their use:

data test2; do obs=1 to 3;output;end;run;

data _NULL_;

dsid=open('test2');

call set(dsid);

do i=1 to 2;

do j=1 to attrn(dsid,'nobs');

obsid=fetchobs(dsid,j);

put obs=;

end;

run;

data _NULL_;

do i=1 to 2;

do j=1 to 3 ;

set test2 point=j ;

put obs= ;

end;

stop;

run;

Unfortunately, none of these two methods are possible with DS2.

Re: How to revisit observations in DS2

FriedEgg — Fri, 17 Oct 2014 12:10:47 GMT

In DS2 you could get set key= functionality using the sqlstmt package

Re: How to revisit observations in DS2

Patrick — Fri, 17 Oct 2014 12:25:36 GMT

You could use the hash or hash iterator package. That should allow you to iterate through the data as many times as you need.

Re: How to revisit observations in DS2

Patrick — Fri, 17 Oct 2014 12:28:55 GMT

I don't have a lot of experience with DS2 - but isn't SQLSTMT only used in a Federation Server context?

Re: How to revisit observations in DS2

JacobSimonsen — Fri, 17 Oct 2014 12:37:24 GMT

I have tought about that solution (the hash and iterator). I think it will work in far most cases. Only problem is, that it require me to load all data into the a hash-object, which in worst case can cause some memory-problems. But it is not so difficult to implement, so I think I will try it out.

If I under stand the suggestion from FriedEgg correct, then I need to create an index on my source data (which is not a problem), and the key= allow me to visit any observations as many times I want. I don't think it will work efficient enugh, my experience with indexes is that it becomes slow if I want to go through all observations in a large dataset. It is only efficient if I only want to look up a few observations from a possible large dataset.

Re: How to revisit observations in DS2

Patrick — Fri, 17 Oct 2014 13:27:19 GMT

I believe - only believe - that what FriedEgg suggests would require the Federation Server. Is this how you're accessing your data?

Re: How to revisit observations in DS2

FriedEgg — Fri, 17 Oct 2014 14:16:24 GMT

You are confusing some not necessarily related things. The SAS Federation Server product and SAS FedSQL language. Which, given their names and such, I don't blame you for.

FedSQL is a implementation, by SAS, of ANSI SQL:1999, and attempts to be a 'vendor neutral SQL' that is compliant with most any DBMS that has a SQL interpreter. FedSQL in SAS is used as the SQL language in PROC FEDSQL and PROC DS2. These procedures and the FedSQL language are in no way dependent on the Federation Server product.

Re: How to revisit observations in DS2

FriedEgg — Fri, 17 Oct 2014 14:21:47 GMT

All I am suggesting is that is it possible to emulate the function of using set with the key= option by using the SQLSTMT package in DS2. The usefulness of said proposition is dependent on the situation at hand and can certainly lead to either excellent or very poor performance (or anywhere in between). As mentioned, another option is to utilize the hash packages. However, both of these choices clearly do not directly mimic set point=, which is certainly a functionality that seems beneficial to have available.

Re: How to revisit observations in DS2

Patrick — Fri, 17 Oct 2014 14:35:56 GMT

Thanks. Good to know. I have definitely some more reading to do.

I've seen the value of DS2 for CI implementations using the Federation Server and I start to understand some of the other areas where it could add value (proc hpds2). I haven't found a real life application in my own line of work yet. But that's may be only because I don't fully understand. Still hoping for some exciting white papers showing how it's actually used in practice (I know that some solutions use it "in the background").

Re: How to revisit observations in DS2

JacobSimonsen — Fri, 17 Oct 2014 15:29:56 GMT

Thank you, both, for your suggestions.

Though, I tend to conclude that DS2 at present will make my program more complex and/or less timeefficient than an ordinary datastep. Even though DS2 has its matrix-package it is easier to write the code in ordinary datastep language (using my homemade matrix package https://communities.sas.com/ideas/1570). That is of course not true in general, just for my present problem. It will be interesting to see how DS2 will be developed in the future.

Re: How to revisit observations in DS2

FriedEgg — Fri, 17 Oct 2014 17:48:55 GMT

One of the most common tasks I utilize PROC DS2 for is model scoring. Have seen significant benefits from the threading for these high compute tasks. Semantically, I also really like DS2. While it may take a few more lines of code to duplicate simple data steps, in many situations I find myself being able to overall reduce complexities, especially with highly recursive routines.

Some additional stream of consciousness thought:

From what I gathered at SGF2014, a lot of focus in DS2 is being placed on integration with Hadoop.

I am very excited about the recent release of the HTTP package and hope the ability to have non fixed and traverse-able data types will come soon so that I can start dynamically ingesting/creating and JSON and XML data to interact with WebService api's more simply.

Re: How to revisit observations in DS2

PGStats — Fri, 17 Oct 2014 18:42:23 GMT

Hi Fried,

what is a non fixed and traverse-able data type?

Re: How to revisit observations in DS2

FriedEgg — Fri, 17 Oct 2014 19:29:43 GMT

A better word may be, non-sized instead of fixed. Is a hierarchical/self-defining data structure, such as JSON/XML that could be received in DS2 without necessary prior knowledge of it's size or components and then traversed as an iterable object to get the key/data components. Similar to a hash object, in essence, where instead of putting a dataset into the object, I instead put a JSON/XML document into it...

Re: How to revisit observations in DS2

jakarman — Sun, 19 Oct 2014 12:53:23 GMT

I am missing the insights on programming language concepts. It is part of "applied computer science" Computer science - Wikipedia, the free encyclopedia as are a lot of other dedicated area-s. The association to a physical piece of hardware "the computer" may be is confusing. In my language the word "informatica" is used. Probably better as it about processing of information.
Using an Array Hash or something is more a data-structure that can be supported by using a language. Data structure - Wikipedia, the free encyclopedia

For transmitting data you are needing conventions for getting meaningfull descriptions. JSOM and XML are examples of those JSON - Wikipedia, the free encyclopedia. These are not of the same kind as data-structures within a programming language. Getting the objects well described is a pre-requisite for understanding them.

The SAS datastep language.

This indication of datastep is needed as SAS is having a lot of languages in their eco-system. Every proc can be an own language on his own. Proc sql - proc ds2 as examples.

The datastep language is designed as a 4GL. It is different to Cobol/Fortran REXX and many more as it is processing the records in an automatic way, by that having a PDV.

The C-language is a weird one as it used to build OS it is more a 2GL (processor level) but also using the same style as Fortran as a 3GL.

With that sequential approach of the datastep language there are advantages and disadvantages.

Advantage: Sequential processing can be extremely efficient when all incoming and outgoing data is also sequential.

Disadvantage: There is a limit on throughput / speed as all actions are a sequential process, by that is missing the opportunity of parallel processing.

I mentioned REXX as a language as that one is very lazy on type declarations like the sas datasteps but it has no PDV. It has no automatic record advancing and by that can interpreted own code language instream.

The array and hash concepts are not of the 4gl within the PDV but of the 3GL level (like Fortran/Cobol). The difference on those concepts 3Gl 4Gl is for many difficult to understand.

The DS2 language

The Proc DS2 is an own language. Some are based on the SCL (SAS/AF) as of methods/declarations. Some are using naming conventions being copies form "R" the packages.

It is an other approach not based on the assumption of sequential processing but supporting multi-threading and supporting more consistent DBMS types.

We will see how it will find a place for usage.

Re: How to revisit observations in DS2

JacobSimonsen — Mon, 10 Nov 2014 17:18:28 GMT

I couldn't get the hash-object solution to work as intended. It is not a problem to define the hash-object and also easy enough to read data into it. But how do I let each thread read different parts of the hash-object. If I use an argument to the thread, then it is the same argument to all threads,

The goal is that the threads should work on different parts of the data - in a way so data can be revisited.

Re: How to revisit observations in DS2

jakarman — Mon, 10 Nov 2014 18:35:08 GMT

Jacob, that makes sense that the hash object is not shared between all different threads. It would only work in a SAS dataset structure as the other threads in a RDBMS are out of reachable area for ds2.

Re: How to revisit observations in DS2

gergely_batho — Mon, 10 Nov 2014 20:21:30 GMT

Maybe the only way to do it, is to load the hash object with the hash.add() method inside the thread (Do not use "datasource" when you declare the hash object! 😞

thread my_thread;

method run();

set my_input;

my_hash.add();

...

end;

endthread;

run;

This way all your hash objects in different threads will be different.

And probably you will use the by statement as well. I think all this makes sense only if you do by group processing.

At the beginning of a group (first.group_var=1) you clear your hash object ( my_hash.clear() ).

At every read you just store the data in the hash object (my_hash.add()).

At the end of a group (last.group_var=1) you start to analyze data in the hash object.

Re: How to revisit observations in DS2

jakarman — Mon, 10 Nov 2014 20:28:21 GMT

Gergely, The question would be can al threads share the same memory set up by one (hash is in memory).

This is requiring a sychronisation process with locking as in a RDBMS and a shared entrypoint for shared memory. I do not expect it is there. It are system programming techniques close to kernel level. The multi-users address spaces where dependent of those.

Of course you can define a hash in every thread, but it will fail in the benefits of hash usage (collecting all random events) while pushing pressure on the memory resource.

Re: How to revisit observations in DS2

gergely_batho — Mon, 10 Nov 2014 20:43:23 GMT

Hi Jaap,

I agree with you. DS2 theards do not share memory.

But my reply was just a short answer/idea on this topic: "The goal is that the threads should work on different parts of the data".

Re: How to revisit observations in DS2

JacobSimonsen — Sat, 15 Nov 2014 23:24:14 GMT

I have succeeded now to write a program where I can revisit the observations. I have used the hash object which I define in each single thread as discussed above.

Unfortunately the method is not time-efficient, but it was a fun experiment anyway. I think the problem is that all threads use the same memory-resource (as Jaap points out). I observe that all my four cpu's are in use during the process, but in total they use only about 25% of total cpu. The log shows that real time usage is only slightly smaller than cpu-time usage.

An other problem is that it is not a very stable method. Most often it run without problems but crashes are not rare.

I have enclosed the program for those who are interested. It is a simple estimation of parameters in a logistic regression where I have a by-group variable.

(only made for test purpose, my real problem is far more complicated than logistic regression)