topic Re: Multiple lines varialbe merge with one line variable (to comparing their value each other pairw in SAS Procedures

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

Garyho — Mon, 14 Feb 2022 07:01:58 GMT

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.

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

Garyho — Mon, 14 Feb 2022 07:13:21 GMT

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.

Re: Multiple lines varialbe merge with one line variable (to comparing their value each other pairw

PeterClemmensen — Mon, 14 Feb 2022 07:17:15 GMT

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?

Re: Multiple lines varialbe merge with one line variable (to comparing their value each other pairw

PeterClemmensen — Mon, 14 Feb 2022 07:20:48 GMT

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

Re: Multiple lines varialbe merge with one line variable (to comparing their value each other pairw

Garyho — Mon, 14 Feb 2022 07:41:44 GMT

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.

Re: Multiple lines varialbe merge with one line variable (to comparing their value each other pairw

PeterClemmensen — Mon, 14 Feb 2022 07:44:01 GMT

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 🙂

Re: Multiple lines varialbe merge with one line variable (to comparing their value each other pairw

Ksharp — Mon, 14 Feb 2022 13:54:54 GMT

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;

Re: Multiple lines varialbe merge with one line variable (to comparing their value each other pairw

Garyho — Mon, 14 Feb 2022 14:31:38 GMT

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

Re: Multiple lines varialbe merge with one line variable (to comparing their value each other pairw

yabwon — Mon, 14 Feb 2022 16:22:33 GMT

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

Re: Multiple lines varialbe merge with one line variable (to comparing their value each other pairw

yabwon — Mon, 14 Feb 2022 16:43:55 GMT

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

Re: Multiple lines varialbe merge with one line variable (to comparing their value each other pairw

Garyho — Tue, 15 Feb 2022 01:51:16 GMT

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.

Re: Multiple lines varialbe merge with one line variable (to comparing their value each other pairw

yabwon — Tue, 15 Feb 2022 10:14:25 GMT

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.htm#n19qtk2v7izsqen1n0am0e5wq268

and some example:

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

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