Multiple lines varialbe merge with one line variable (to comparing their value each other pairwise)

[Garyho]

I have a dataset like this( I'll just list a few lines, actually it might be  many line  such as 20 30 ..):

subjid  value

001     12

002     25

003     30

004     6

004     9

.....

And  I need to comparing their value each other  pairwise ,so  I  want to output:

subjid  value value_1  value_2  value_3  value_4   value_5

001     12           12            25            30          6           9

002     25          12            25            30          6           9

003     30          12            25            30          6           9

004     6            12            25            30          6           9

004     9           12            25            30          6           9

.......

Dear SAS expert,

Could you create a macro program to do that ?? many thank.

[PeterClemmensen]

This seems to be part of a larger task. And if so, there is definitely a better way to get there than to create columns for all of your observations. 

What is the real task here?

[Garyho]

Actually real task here is that comparing survive stata pairwise for subject. The one surviving longer than the other one then get +1 point; the one surviving shorter than the other one then get -1 point; if surviving time is the same then get 0 point. Finally calculate the sum point per subject.

Now I think of a way that merge per subject value into same raw and calculate.

If you any better way of idea, please kindly help.

[PeterClemmensen]

Ok. Can you explain your problem from your sample data?

What does your desired result look like from that? Makes it much easier to provide a usable code answer 🙂

[Garyho]

data subject;
input subject survive_state ;
cards;
1001 0
1002 1
1003 4
1004 5
1005 2

;
run;

data subject_survive;
input subject survive_state survive_state_1001 survive_state_1002 survive_state_1003 survive_state_1004 survive_state_1005;
cards;
1001 0 0 1 4 5 2
1002 1 0 1 4 5 2
1003 4 0 1 4 5 2
1004 5 0 1 4 5 2
1005 2 0 1 4 5 2
;
run;

data final;
set subject_survive;
length total_1 total_2 total_3 total_4 total_all 8.;

if subject=1001 then do;

if survive_state<survive_state_1002 then total_1=-1;
if survive_state<survive_state_1003 then total_2=-1;
if survive_state<survive_state_1004 then total_3=-1;
if survive_state<survive_state_1005 then total_4=-1;

if survive_state=survive_state_1002 then total_1=0;
if survive_state=survive_state_1003 then total_2=0;
if survive_state=survive_state_1004 then total_3=0;
if survive_state=survive_state_1005 then total_4=0;

if survive_state>survive_state_1002 then total_1=1;
if survive_state>survive_state_1003 then total_2=1;
if survive_state>survive_state_1004 then total_3=1;
if survive_state>survive_state_1005 then total_4=1;
total_all=sum(total_1,total_2,total_3,total_4);
end;


if subject=1002 then do;

if survive_state<survive_state_1001 then total_1=-1;
if survive_state<survive_state_1003 then total_2=-1;
if survive_state<survive_state_1004 then total_3=-1;
if survive_state<survive_state_1005 then total_4=-1;

if survive_state=survive_state_1001 then total_1=0;
if survive_state=survive_state_1003 then total_2=0;
if survive_state=survive_state_1004 then total_3=0;
if survive_state=survive_state_1005 then total_4=0;

if survive_state>survive_state_1001 then total_1=1;
if survive_state>survive_state_1003 then total_2=1;
if survive_state>survive_state_1004 then total_3=1;
if survive_state>survive_state_1005 then total_4=1;
total_all=sum(total_1,total_2,total_3,total_4);
end;

if subject=1002 then do;

if survive_state<survive_state_1001 then total_1=-1;
if survive_state<survive_state_1003 then total_2=-1;
if survive_state<survive_state_1004 then total_3=-1;
if survive_state<survive_state_1005 then total_4=-1;

if survive_state=survive_state_1001 then total_1=0;
if survive_state=survive_state_1003 then total_2=0;
if survive_state=survive_state_1004 then total_3=0;
if survive_state=survive_state_1005 then total_4=0;

if survive_state>survive_state_1001 then total_1=1;
if survive_state>survive_state_1003 then total_2=1;
if survive_state>survive_state_1004 then total_3=1;
if survive_state>survive_state_1005 then total_4=1;
total_all=sum(total_1,total_2,total_3,total_4);
end;

if subject=1003 then do;

if survive_state<survive_state_1001 then total_1=-1;
if survive_state<survive_state_1002 then total_2=-1;
if survive_state<survive_state_1004 then total_3=-1;
if survive_state<survive_state_1005 then total_4=-1;

if survive_state=survive_state_1001 then total_1=0;
if survive_state=survive_state_1002 then total_2=0;
if survive_state=survive_state_1004 then total_3=0;
if survive_state=survive_state_1005 then total_4=0;

if survive_state>survive_state_1001 then total_1=1;
if survive_state>survive_state_1002 then total_2=1;
if survive_state>survive_state_1004 then total_3=1;
if survive_state>survive_state_1005 then total_4=1;
total_all=sum(total_1,total_2,total_3,total_4);
end;


if subject=1004 then do;

if survive_state<survive_state_1001 then total_1=-1;
if survive_state<survive_state_1003 then total_2=-1;
if survive_state<survive_state_1002 then total_3=-1;
if survive_state<survive_state_1005 then total_4=-1;

if survive_state=survive_state_1001 then total_1=0;
if survive_state=survive_state_1003 then total_2=0;
if survive_state=survive_state_1002 then total_3=0;
if survive_state=survive_state_1005 then total_4=0;

if survive_state>survive_state_1001 then total_1=1;
if survive_state>survive_state_1003 then total_2=1;
if survive_state>survive_state_1002 then total_3=1;
if survive_state>survive_state_1005 then total_4=1;
total_all=sum(total_1,total_2,total_3,total_4);
end;

if subject=1005 then do;

if survive_state<survive_state_1001 then total_1=-1;
if survive_state<survive_state_1003 then total_2=-1;
if survive_state<survive_state_1002 then total_3=-1;
if survive_state<survive_state_1004 then total_4=-1;

if survive_state=survive_state_1001 then total_1=0;
if survive_state=survive_state_1003 then total_2=0;
if survive_state=survive_state_1002 then total_3=0;
if survive_state=survive_state_1004 then total_4=0;

if survive_state>survive_state_1001 then total_1=1;
if survive_state>survive_state_1003 then total_2=1;
if survive_state>survive_state_1002 then total_3=1;
if survive_state>survive_state_1004 then total_4=1;
total_all=sum(total_1,total_2,total_3,total_4);
end;
run;

This is sample code to help you realize question .Dataset subjectis raw dataset. and I want to transport Dataset subjectis to
subject_survive,but now I has no idea how to transport effectively in macro code. And fianlly, What result I want is like dataset final.
I appreciate very much if have any idea in macro step to resolve.
a

[Garyho]

005     9 ..... And  I need to comparing their value each other  pairwise ,so  I  want to output: subjid  value value_1  value_2  value_3  value_4   value_5 001     12           12            25            30          6           9 002     25          12            25            30          6           9 003     30          12            25            30          6           9 004     6            12            25            30          6           9 005     9           12            25            30          6           9 ....... Dear SAS expert, Could you create a macro program to do that ?? many thank.

[PeterClemmensen]

@Garyho , Also, I merged your two similar questions into one 🙂

[Ksharp]

data have;
input subjid  value;
format subjid z3.;
cards;
001     12
002     25
003     30
004     6
004     9
;

proc transpose data=have out=temp prefix=value_;
var value;
run;

data want;
 set have;
 if _n_=1 then set temp(drop=_name_);
run;

[yabwon]

Just for complete case I would add:

data want;
 set have;
 if _n_=1 then set temp(drop=_name_);

 array V value_:;

 compare = 0;
 do over V;
  compare + sign(value - V);
 end;
run;

Bart

[yabwon]

Just for fun, one more approach without transposing, just with sorting (and few more obs for testing "ties").

data have;
input subjid  value;
format subjid z3.;
cards;
001     12
002     25
003     30
004     6
005     9
006     25
007     6
;

data want;
  do _N_ = 1 by 1 until(last.value);
    set want nobs = nobs curobs = curobs;
    by value;
    if first.value then compare = (curobs - 1);
    if last.value  then compare = compare - (nobs - curobs);
  end;

  do _N_ = 1 to _N_;
    set want;
    output;
  end;
run;
proc print;
run;

[EDIT] if there are no ties it reduces to:

data have;
input subjid  value;
format subjid z3.;
cards;
001     12
002     25
003     30
004     16
005     9
006     26
007     6
;
run;

proc sort data = have out = want;
  by value;
run;

data want;
    set want nobs = nobs curobs = curobs;
    compare = (curobs - 1) - (nobs - curobs);
run;
proc print;
run;

Bart

[Garyho]

This is magic code I had never seen before. I run your code in sas eg 9.3 and a error message hanppen in log file ,like :

ERROR 22-322: Syntax error, expecting one of the following: END, INDSNAME, KEY, KEYS, NOBS, POINT.
ERROR 76-322: Syntax error, statement will be ignored.

it might something wrong in code "curobs = curobs;"

And I know little about curobs funstion , May I ask question that What is curobs's role in code??

Any way, Thank you very much for providing many new ideas and code.

[yabwon · Accepted solution]

9.3... that may be the reason why it is not working. Probably, the curobs= option was introduced in 9.4.

Here is the documentation for the SET statement: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/lestmtsref/p00hxg3x8lwivcn1f0e9axziw57y.ht...

and some example:

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/lestmtsref/p00hxg3x8lwivcn1f0e9axziw57y.ht...

Curobs= creates a variable which keeps the number of currently read observation (the real observation number from the data set, so even if WHERE is applied it will return the real obs number).

In case there are no ties in your data on the VALUE variable you can use the following code (since here the _N_ imitates the curobs= quite well):

data have;
input subjid  value;
format subjid z3.;
cards;
001     12
002     25
003     30
004     16
005     9
006     26
007     6
;
run;

proc sort data = have out = want;
  by value;
run;

data want;
    set want nobs = nobs;
    compare = (_N_ - 1) - (nobs - _N_);
run;
proc print;
run;

Bart