topic Re: Apply count variable to all observations in SAS Programming

Apply count variable to all observations

LukeD — Tue, 15 Oct 2013 16:39:38 GMT

Hi,

I am trying to calculate how many times different suppliers have provided a name and address.

What I need is for the value of the supplied variable for last.UID to be applied to all observations of that UID so that when I later class by each supplier I get an accurate count of the total time that record has been supplied.

Current code:

data new;

set supplied1;

by uid;

if first.uid then do;

supplied=0;

end;

supplied=supplied+1;

retain supplied;

if last.uid;

run;

I can do this in a number of steps but I have a feeling there is a much simpler way of doing it!

Thanks

Re: Apply count variable to all observations

art297 — Tue, 15 Oct 2013 17:33:13 GMT

I think you will have to make two passes through the data, even if only one datastep is used. One solution:

data new;

do until (last.uid);

set supplied1;

by uid;

if first.uid then supplied=1;

else supplied=supplied+1;

end;

do until (last.uid);

set supplied1;

by uid;

output;

end;

run;

Re: Apply count variable to all observations

Peter_C — Tue, 15 Oct 2013 19:42:42 GMT

There are merits in this approach. Although there appears to be 2 passes through the data because there are two set statemets, the data flow switches from one stream to the other on change of by group. If the buffering and caching of the data holds more than one by group there will be only one I/O stream into the buffers for this data step.

It has been presented before in forums and sas-L - using two references to to same dataset in a single SET statement like :

Data final ;

SET supplied1( in= A ) supplied1 ;

By uid ;

If first.uid then supplied=0;

If a then supplied +1 ;

else output;

Run;

Re: Apply count variable to all observations

art297 — Tue, 15 Oct 2013 20:11:47 GMT

: Don't know how I missed that but, in the tests I just ran, both approaches took approximately the same amounts of CPU and real times, thus I'm not convinced that they don't both take two passes through the data. However, that said, I like the approach you suggested as it does the same thing with less code. The only thing I'd suggest changing is the if then else combinations. i.e.:

data new;

set supplied1( in= a ) supplied1 ;

by uid ;

if first.uid then supplied=1;

else if a then supplied +1 ;

else output;

run;

Re: Apply count variable to all observations

Linlin — Tue, 15 Oct 2013 20:29:11 GMT

shorter:

data have;

input id@@;

cards;

1 2 2 3 3 3 9 9 9 9 18 18 18 18 18

;

data want;

set have(in=a)have;

by id ;

if a then t+1-first.id*t;

else output;

run;

proc print;run;

Re: Apply count variable to all observations

art297 — Tue, 15 Oct 2013 20:43:23 GMT

: Actually, on average, your suggestion runs slightly slower, but the two approaches do run in very similar times. I prefer the if-then-else combinations as it is easier (I think) for most to follow the logic. The fact that it run slightly faster (and I mean really, really only slightly), I think, is just an added benefit.

Re: Apply count variable to all observations

Linlin — Tue, 15 Oct 2013 20:48:17 GMT

Hi Art,

You are right. Is there any leftover turkey to share?

Re: Apply count variable to all observations

art297 — Tue, 15 Oct 2013 21:06:22 GMT

@Linlin: Only if you get here quickly and now is being served in the form of Sheppard's Pie

Re: Apply count variable to all observations

Astounding — Tue, 15 Oct 2013 21:22:22 GMT

You won't measure the difference in a one-variable data set, but it would be good practice to modify the SET statement:

set supplied1 (in=a keep=uid) supplied1;

Re: Apply count variable to all observations

art297 — Tue, 15 Oct 2013 21:45:35 GMT

: Interestingly, the efficiency doesn't appear to apply in this particular situation. The test code I ran was:

data supplied1;

set sashelp.class (rename=(age=uid));

array junk(999) (999*1);

do _n_=1 to 10000;

output;

end;

run;

proc sort data=supplied1;

by uid;

run;

data new;

set supplied1( in= a ) supplied1 ;

by uid ;

if first.uid then supplied=0;

if a then supplied +1 ;

else output;

run;

data new;

set supplied1( in= a keep=uid) supplied1 ;

by uid ;

if first.uid then supplied=0;

if a then supplied +1 ;

else output;

run;

Re: Apply count variable to all observations

Astounding — Tue, 15 Oct 2013 22:41:37 GMT

Art,

Hmmmm ... there are mysteries that can be difficult to explain. By any chance did you measure the difference with your original DOW approach?

Re: Apply count variable to all observations

art297 — Tue, 15 Oct 2013 22:53:35 GMT

: of course I looked at that as well. Even with the double DOW, the pdv is populated with the variables from both steps thus using a keep option doesn't improve the processing times.

If any of you are wondering why is surprised, take a look at: Increase Your Productivity by Doing Less - sasCommunity

Re: Apply count variable to all observations

Haikuo — Wed, 16 Oct 2013 02:38:41 GMT

Art's paper reminds me that one of the co-authors, Ksharp, has dropped out of the grid for a long time. Let's wish him the best!

Haikuo

Re: Apply count variable to all observations

art297 — Wed, 16 Oct 2013 03:20:20 GMT

: KSharp is still alive and well and, every now and then, sends me an idea for a SAS-related paper that he'd like me to investigate.

Re: Apply count variable to all observations

ScottBass — Wed, 16 Oct 2013 07:31:09 GMT

Hi,

Luke's original post said "...there must be a simpler way of doing this", but many of the replies are focused on performance.

If you discard simplicity and go for performance, and if your data will fit in memory, then IIRC you can use a hash object for this, and do this in one pass of the data.

I don't have time to look it up now, but hit the doc on the hash object and search on "suminc" (I think). Anyway, there's an example in there of deriving a sum based on the keys (your by variable), and you then use the output statement to write out the results after you've hit EOF. (I think it's kinda cool you can write a permanent dataset from a data _null_ step!)

HTH,

Scott

Re: Apply count variable to all observations

ScottBass — Fri, 18 Oct 2013 07:21:29 GMT

Hi All,

I had a further chance to "play" with this, and thought I'd share my code.

Luke's original post only produced a frequency count per UID, due to his subsetting if statement on last.uid. However, his text said:

What I need is for the value of the supplied variable for last.UID to be applied to all observations of that UID so that when I later class by each supplier I get an accurate count of the total time that record has been supplied.

This is a bit ambiguous; does Luke just want a frequency count per UID, as his code generates, or does he want the frequency count to be "applied to all observations"? Most of the suggested approaches assumed the latter.

The double set statement "set supplied1 (in=a) supplied1; by uid" is a pretty cool construct I'd not seen before. I walked it through the data step debugger to see that it reads all observations in the first by group from the first dataset, then all the observations in the first by group from the second dataset, etc. If I recall my terminology correctly, it is interleaving the data from the two datasets by UID "chunks". This then allows you to derive the frequency counts from the first dataset (in=a), then "merge" them back into the observations from the second dataset.

This is analogous to the message you often see in SQL "The query requires remerging summary statistics back with the original data."

I "played" with the different approaches, trying to see how I could optimize the performance. I've attached my .sas file and .log file when I ran it on my server.

Comments:

1) I've been using a "SPDE Work" library a lot lately to get better I/O performance in my large jobs. It's a real easy change to make, with some caveats (potential file locking, no support for views, doesn't support sasfile, and so on). See the attached file for more details.

2) Even though we're doing a double read of the data on disk, I didn't get an appreciable performance gain by using sasfile, and then doing the double read from memory.

3) I didn't get the expected performance gains using a hash object. And given that, no one would approach the problem this way. It was just a chance for me to "play" and learn more about hash object key summaries. See SAS(R) 9.3 Language Reference: Concepts, Second Edition, scroll to "Maintaining Key Summaries", for more details. And I'm not sure my code is optimized; while I do first read the data into memory and then iterate on the copy in memory, I suspect having to iterate over the hash introduces overhead. Using a DOW loop might be more efficient.

Anyway, I hope some of this is useful

Scott

Re: Apply count variable to all observations

ChrisNZ — Wed, 23 Oct 2013 23:20:35 GMT

For the sake of code clarity,

set SUPPLIED1(in=a keep=uid) SUPPLIED1;

is indeed better imho, regardless of performance.

This way we have more information about what each table is used for.

We could even write:

set SUPPLIED1(in=groupingstream keep=uid) SUPPLIED1;

set SUPPLIED1(in=groupingstream keep=uid) SUPPLIED1(in=outputstream);

if we were really petty about descriptive names, which we are not.

Re: Apply count variable to all observations

Peter_C — Sun, 03 Nov 2013 17:45:42 GMT

Hi Scott

in case no one else has said it, thank you for the depth of your investigation, the results and the detail of your review.

I also respond to discuss your suggestion:

"This is analogous to the message you often see in SQL "The query requires remerging summary statistics back with the original data."

imho the data step is not "remerging" as sql does, because this "remerge" occurs only for the rows of a/each by-group whereas for sql it will be storing the whole table in spool, cache or memory before performing a whole-table merge (at least in my experience of teradata sql).

OK when there is no by-statement or there is only one value for the by-variable the data step of this style would indeed be equivalent to an "sql remerge".

regards

peterC

Re: Apply count variable to all observations

ScottBass — Mon, 04 Nov 2013 00:36:56 GMT

Hi Peter,

Thanks for your comments. I just meant that SQL is first deriving the summary statistics (under the covers), then remerging with the original data. I didn't mean to imply that the two processes (data step vs. sql) were identical.

Regards,

Scott