Help using Base SAS procedures

Looking inside a big ID group and add a column

Reply
Contributor
Posts: 62

Looking inside a big ID group and add a column

Hi,

I've got a problem, and I think the solution can't be very hard. But... I can't get the job done! Hopefully someone can help me on this one!

I've got a dataset with 3 rows:
Date - CustomerID - Status

The dataset contains a set of dates (from 2008 until now, per month) for every customer ID. For every month, the status (0, 1, 2, 3, etc) has been set for every customer. Example:

01-01-08 - Customer1 - 0
01-02-08 - Customer1 - 0
01-03-08 - Customer1 - 1
01-04-08 - Customer1 - 0
01-05-08 - Customer1 - 1
01-06-08 - Customer1 - 2
01-07-08 - Customer1 - 1
etc., until the date 01-08-10 is reached, and on that moment, Customer 2 is presented.

What I want, is to add another column, which reflects the status ("Hold" if the status was the same the month before and the status > 0, "Forward" if the status is higher than the month before, "Back" if the status is lower than the month before (except for status 0) and "None" if the status reaches "0" when the status a month ago was higher than 0. If the status is 0 in a specific month and was also 0 a month ago, then I like to add the word "Zero" to the new column).

So, I was trying things with "if first.customerID", but with this approach you can only get the figures for the first and last customerID.

Can anyone help me on this one? Thanks in advance!!!
Frequent Contributor
Posts: 106

Re: Looking inside a big ID group and add a column

Go to http://www.sascommunity.org and search for "dow".

I tried to search/find samples from 'data _null_' demonstrating this technique but his(her? I don't remember) userid doesn't lend itself to do userid based searches both in these forums and in the SAS-L archive either.

(@data _null_: should you read this maybe you can lend us a hand on how to search for your extremely valuable contributions 8) )

Kind regards
Robert
Respected Advisor
Posts: 3,777

Re: Looking inside a big ID group and add a column

I'm not sure a HoWDy DO as I like to call it: after Henderson; who first wrote about it in 1983, Whitlock; who is reported to have been the first to use it in a SAS-L post and Dorfman; who popularized the name DOW, in many outstanding SGF/SUGI papers, is necessary for this problem. But it could be used.

I don't know how to search for my screen name. I will try to figure that out.

Here is my take on the problem, I had a bit of trouble with the specs for the variable I call INFO, so this may not be right. I think an important factor here is to find the DIF for each month. Then use DIF and STATUS compute INFO.

[pre]
data test;
input date :ddmmyy. id :$9. status;
format date ddmmyyd8.;
cards;
01-01-08 Customer1 0
01-02-08 Customer1 0
01-03-08 Customer1 1
01-04-08 Customer1 0
01-05-08 Customer1 1
01-06-08 Customer1 2
01-07-08 Customer1 1
;;;;
run;


data test;
set test;
by id;
dif = dif(status);
if first.id the dif = 0;
if status gt 0 then select(sign(dif));
when(-1) info = 'Back ';
when(0) info = 'Hold ';
when(1) info = 'Forward';
end;
if status eq 0 then select(sign(dif));
when(-1) info = 'None';
when(0) info = 'Zero';
when(1) info = 'Forward';
end;
run;
proc print;
run;
[/pre]
PROC Star
Posts: 7,364

Re: Looking inside a big ID group and add a column

Robert (and anyone else who'd like to know),

You can find virtually all of data_null_'s posts on SAS-L and comp.soft-sys.sas if you do 4 searches using the excellent search interface that Lex Jansen provides:
http://www.lexjansen.com/sugi/search_sas-l.php

You have to search both the UofG and Google twice looking for author's address is or contains:

datan
and
iebupdte

Of course, that won't get you his/her excellent SAS forum posts (hmm: Lex any chance of adding yet another search menu to the screen?), but it will provide quite a few years of excellent reading.

Art
Valued Guide
Posts: 2,175

Re: Looking inside a big ID group and add a column

I'm going to update the posting on forum mark-up to show how to find a users postings (just click on her/his name on the left)

or, have I missed the point?
Frequent Contributor
Posts: 106

Re: Looking inside a big ID group and add a column

Based on mutual context, yes, slightly ;-)

The problem can be recreated by starting with the "Search all forums" box which leads to http://support.sas.com/forums/search.jspa?q=dow . There I entered "data _null_" in the userid field which resulted in "Failed to find specified user". Searching SAS-L's archive failed similarly.

Art T. suggested searching for datan which is a viable alternative.
Valued Guide
Posts: 2,175

Re: Looking inside a big ID group and add a column

The search forums form offers selection by users forum ID, but cannot find my superficial ID Peter.C (not even when pasted from the screen !)
but it is there
and it works
but it needs
the userID
468858 for "data _null;"
287360 for Robert.Bardos
a forum ID as a number
click on name link on left and examine browser address bar
http://support.sas.com/forums/profile.jspa?userID=287360

looks like a web programmers defect

regards
Peter.C
aka
392339
Contributor
Posts: 62

Re: Looking inside a big ID group and add a column

Thank you very much, this looks very logical and promising!! Unfortunately, I can't apply it until coming monday (work), but I'll let you know.
N/A
Posts: 0

Re: Looking inside a big ID group and add a column

data test;
input date :ddmmyy. id :$9. status;
format date ddmmyyd8.;
cards;
01-01-08 Customer1 0
01-02-08 Customer1 0
01-03-08 Customer1 1
01-04-08 Customer1 0
01-05-08 Customer1 1
01-06-08 Customer1 2
01-07-08 Customer1 1
01-01-08 Customer2 2
01-02-08 Customer2 1
01-03-08 Customer2 1
01-04-08 Customer2 3
01-05-08 Customer2 4
01-06-08 Customer2 2
01-07-08 Customer2 0
;;;;
run;
proc sort data=test;
by id;
run;
data test1;
set test;
by id;
pt=_N_-1;
set test(keep=status rename=(status=status_b)) point=pt;
if first.id=1 then status_b=.;
run;
data test2;
set test1;
by id;
if status>status_b then info='forward';
else if status>0 and status=status_b then info='hold';
else if status^=0 and status else if status=0 and status_b>0 then info='none';
else if status=0 and status_b=0 then info='zero';

where status_b^=.;
run;
proc print;
run;
Ask a Question
Discussion stats
  • 8 replies
  • 132 views
  • 0 likes
  • 6 in conversation