Date: Fri, 22 Jun 2007 18:08:00 -0400
Reply-To: Ya Huang <ya.huang@AMYLIN.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ya Huang <ya.huang@AMYLIN.COM>
Subject: Re: please help me to solve this order problem
Content-Type: text/plain; charset=ISO-8859-1
Here is one way:
data xx;
input id x1 x2 x3 x4 x5 x6 x7;
cards;
1 0 1 0 0 1 0 0
2 3 0 1 0 15 15 0
4 26 1 0 46 46 0 0
12 0 1 0 5 5 0 47
15 3 1 3 16 16 47 47
31 0 1 0 29 0 29 37
32 0 1 0 0 0 0 0
50 1 1 17 48 12 5 0
;
data xx1;
set xx;
array allx x1-x7;
do over allx;
val=allx;
vnm=vname(allx);
output;
end;
keep id val vnm;
run;
proc sort;
by id val;
run;
data xx2;
set xx1;
by id val;
if first.id then n_=0;
if first.val and ^first.id then n_+1;
run;
proc transpose data=xx2 out=xx3 (drop=_name_);
by id;
var n_;
id vnm;
run;
data want;
retain id x1-x7;
set xx3;
run;
proc print;
run;
id x1 x2 x3 x4 x5 x6 x7
1 0 1 0 0 1 0 0
2 2 0 1 0 3 3 0
4 2 1 0 3 3 0 0
12 0 1 0 2 2 0 3
15 1 0 1 2 2 3 3
31 0 1 0 2 0 2 3
32 0 1 0 0 0 0 0
50 1 1 4 5 3 2 0
On Fri, 22 Jun 2007 21:30:57 -0000, sylia <sylia.chen@GMAIL.COM> wrote:
>Hi, guys,
>
>I have a very tricky problem of re-assign order of different vars. I
>have 7 vars, each indicate a product purchase time. For ids who not
>purchase at all, the 7 vars are all 0. For other ids, the not-0-
>numbers means the time point of the product purchase. Some ids may
>purchase more than one product at the same time point. In this case,
>the number for those vars are same.
>
>I need to assign an purchase order of these 7 vars, which means, I
>need to use 1 to 7 to order this number instead of the real time point
>number between 1 to 48.
>
>My boss's requirement is：for time point =1, keep the order =1;
> for the next purchased pdt(s),
>order=2; if more than one pdt, then order all =2;
> for the third purchased pdt(s),
>order should be the real count; etc.
>
>e.g., for id=1, order of x1, x3, x4, x6, & x7=0, order of x2 & x5=1,
> ofr id=2, order of x2, x4, x7=0, order of x3=1, order of x1=2,
>order of x5, x6=3
>
>the samle data is as below.
>
>Since my data is very large (more than 10 thousand), it's no way to
>count by hand. I don't know how to solve this problem, please help me
>if you have any ideas. Thank you very much!
>
>
>=====================================
>id x1 x2 x3 x4
>x5 x6 x7
>1 0 1 0 0 1 0 0
>2 3 0 1 0 15 15 0
>3 0 0 0 0 0 1 0
>4 26 1 0 46 46 0 0
>5 0 1 0 0 0 0 0
>6 0 0 0 0 0 0 0
>7 0 0 0 0 0 0 0
>8 0 1 0 1 1 0 0
>9 0 0 0 0 0 0 1
>10 0 0 0 0 0 1 0
>11 0 0 0 0 0 0 0
>12 0 1 0 5 5 0 47
>13 0 1 0 0 0 0 0
>14 0 1 0 0 0 0 0
>15 3 1 3 16 16 47 47
>16 0 1 0 0 0 0 0
>17 0 1 0 0 0 0 0
>18 0 1 0 0 0 0 0
>19 0 1 0 0 0 0 0
>20 0 1 0 0 0 0 0
>21 0 1 0 0 0 0 0
>22 0 1 0 0 0 0 0
>23 0 0 0 0 0 1 0
>24 0 0 0 0 1 0 0
>25 1 0 0 0 0 0 0
>26 0 0 0 1 0 0 0
>27 0 0 0 0 0 0 0
>28 1 0 0 0 0 0 0
>29 0 0 0 0 0 0 1
>30 0 1 0 0 0 0 0
>31 0 1 0 29 0 29 37
>32 0 1 0 0 0 0 0
>33 0 0 0 0 0 0 0
>34 0 0 0 0 0 0 0
>35 0 0 0 0 0 0 0
>36 0 0 0 0 0 0 0
>37 0 0 0 0 0 0 0
>38 0 0 0 0 0 0 0
>39 1 0 0 0 0 0 0
>40 0 1 0 0 0 0 0
>41 0 0 0 0 0 0 0
>42 0 0 0 0 0 0 0
>43 0 0 0 0 0 0 0
>44 0 1 0 0 0 0 0
>45 1 0 0 0 0 0 0
>46 0 0 0 0 0 0 0
>47 0 1 0 0 0 0 0
>48 0 0 0 0 0 0 0
>49 0 0 0 0 0 0 0
>50 1 1 17 48 12 5 0
>
>==============================
>
>Thank you again!
>
>Sy
| 
