BookmarkSubscribeRSS Feed
Wouter
Obsidian | Level 7
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!!!
8 REPLIES 8
Robert_Bardos
Fluorite | Level 6
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 😎 )

Kind regards
Robert
data_null__
Jade | Level 19
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]
art297
Opal | Level 21
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
Peter_C
Rhodochrosite | Level 12
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?
Robert_Bardos
Fluorite | Level 6
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.
Peter_C
Rhodochrosite | Level 12
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
Wouter
Obsidian | Level 7
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.
deleted_user
Not applicable
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 791 views
  • 0 likes
  • 6 in conversation