BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

Dear,

 

i need help in my code. I trying to create find best response in the data.

I have to follow:1. best time response before orres='pd' or up to visit='cy6' and follow  order cr>pr>sd>pd>ne

 

output  needed;

id      param        aval       visit            date

 1           bor         pr        cy6            2016-11-10

2           bor         sd           cy4           2016-08-02

3           bor         cr           cy4           2016-08-02

4           bor         cr           cy4           2016-08-02

5           bor         pr           cy5           2016-11-10

 

data one;
input id test $ orres $ visit $ date $10.; datalines; 1 ovrp sd cy5 2016-08-02 1 ovrp pr cy6 2016-11-10 1 ovrp pd cy7 2016-12-10 2 ovrp sd cy4 2016-08-02 2 ovrp pd cy5 2016-11-10 2 ovrp pd cy6 2016-11-10 3 ovrp cr cy4 2016-08-02 3 ovrp pr cy5 2016-11-10 3 ovrp pd cy6 2016-11-10 4 ovrp cr cy4 2016-08-02 4 ovrp pr cy5 2016-11-10 5 ovrp cr cy4 2016-08-02 5 ovrp pr cy5 2016-11-10 5 ovrp sd dvs 2016-11-10 ; proc sort data=one; by id date visit; run; data two; retain param avalc adt; do until(last.id); set one; by id; if test='pd' or visit='cy6' then do; param='bor'; avalc=orres; adt=input(date,is8601da.); format adt yymmdd10.; output; end; end; run;  

 

 

10 REPLIES 10
ChrisNZ
Tourmaline | Level 20

1.Why this output?

3           bor         cr           cy4           2016-08-02

There is is no 'pd' or 'cy6'.

 

2. What does this mean?

follow  order cr>pr>sd>pd>ne

knveraraju91
Barite | Level 11

Sorry. i did not explain clearly. 

pd=progressive disease

bor=best respose

 

For each subject I have to find 'bor' before the patient on or before visit='cy6' or befoe='pd'.

if there are none, then I need to pick the best by following order(cr>pr>sd>pd>ne).

 

For id=4, there is no orres=' pd'  and visit='cy6' so  the bor='cr' because he has 'cr' at visit=cy4 and 'pr' at visit='cy5'. By following order(cr>pr>sd>pd>ne)  means if there are orres='cr' and orres='pr'  then take orres='cr'.

Thank you

 

output  needed;

id      param        aval       visit            date

 1           bor         pr        cy6            2016-11-10

2           bor         sd           cy4           2016-08-02

3           bor         cr           cy4           2016-08-02

4           bor         cr           cy4           2016-08-02

5           bor         pr           cy5           2016-11-10

ChrisNZ
Tourmaline | Level 20

Like this?

data ONE ;
  input ID TEST $ ORRES $ VISIT $ DATE $10.;
datalines;
1 ovrp sd cy5 2016-08-02 
1 ovrp pr cy6 2016-11-10
1 ovrp pd cy7 2016-12-10
2 ovrp sd cy4 2016-08-02 
2 ovrp pd cy5 2016-11-10
2 ovrp pd cy6 2016-11-10
3 ovrp cr cy4 2016-08-02 
3 ovrp pr cy5 2016-11-10
3 ovrp pd cy6 2016-11-10
4 ovrp cr cy4 2016-08-02 
4 ovrp pr cy5 2016-11-10
5 ovrp cr cy4 2016-08-02 
5 ovrp pr cy5 2016-11-10
5 ovrp sd dvs 2016-11-10
;
proc sort data=ONE out=SORTED(index=(ID_ORRES=(ID ORRES)));
  by ID DATE  VISIT;
run;
data TWO; 
  set SORTED;
  by ID;

  %* init;
  if first.ID then ID_DONE=0;
  if ID_DONE=0;

  %* case 1 ;
  if VISIT='cy6' then do;       
    ID_DONE+1;
    output;
    return;
  end;

  %* case 2 ;
  NEXTOBS=_N_+1;
  set SORTED(keep=ID ORRES rename=(ID=NEXT_ID ORRES=NEXT_ORRES) ) point=NEXTOBS;
  if ID=NEXT_ID and NEXT_ORRES='pd' then do;  
    ID_DONE+1;
    output;
    return;
  end;

  %* case 3 ;
  if last.ID then do ORRES='cr','pr','sd','pd','ne';   
    set SORTED key=ID_ORRES ;
    if _IORC_ = 0 then do;
      output;
      leave;
    end;
  end;
  keep ID TEST ORRES VISIT DATE ;
run;
ID TEST ORRES VISIT DATE
1 ovrp pr cy6 2016-11-10
2 ovrp sd cy4 2016-08-02
3 ovrp pr cy5 2016-11-10
4 ovrp cr cy4 2016-08-02
5 ovrp cr cy4 2016-08-02

 

 

 

Shmuel
Garnet | Level 18

Assig a new variable ( a sort key) depending on ORRES so that sort wiil output the data in the right, required, order.

After sort output the first or last obs in ID, depending on the code order:

 

/* order required: order cr>pr>sd>pd>ne */
proc format lib=work;
   value $sort
        'cr' = '1'
        'pr' = '2'
        'sd' = '3'
        'pd' = '4'
        'ne' = '5'
 ; run;
data temp;
 set one;
       sort_key = put(orres, $sort1.);
run;
proc sort data=temp; by ID sort_key; run;

data want;
 set temp;
   by ID sort_key;
        if first.sort_key then output;  /* assumed CR is the best */
  /*   if last.sort_key then output;  ** assumed NE is the best */
run;
 
knveraraju91
Barite | Level 11

Thank you very much for the help. It will take some time for me to understand your code.

 

The output for id=3 should be orres='cr'. But the output i am getting is orres='pr'. As per the order((cr>pr>sd>pd>ne) I have to take the best response if present. Thank you

 

 

ChrisNZ
Tourmaline | Level 20

No. You said  the order((cr>pr>sd>pd>ne)   is only if cy6 is not found. There is cy6 for ID =3.

knveraraju91
Barite | Level 11

Sorry for the confusion.

My specs says: I have to look until "first orres='pd' or visit='cy6' " which ever comes first then I have pick from the order  (cr>pr>sd>pd>ne). That means, if there above  'cr ' and 'pr'  until then  aval='cr' because that is the best observed response till then.

 

If there are no orres='pd' and visit='cy6' then I have to take last obs.

 

Thank you very much

ChrisNZ
Tourmaline | Level 20

This matches the new specs, but still not your original output.

data ONE ;
  input ID TEST $ ORRES $ VISIT $ DATE $10.;
datalines;
1 ovrp sd cy5 2016-08-02 
1 ovrp pr cy6 2016-11-10
1 ovrp pd cy7 2016-12-10
2 ovrp sd cy4 2016-08-02 
2 ovrp pd cy5 2016-11-10
2 ovrp pd cy6 2016-11-10
3 ovrp cr cy4 2016-08-02 
3 ovrp pr cy5 2016-11-10
3 ovrp pd cy6 2016-11-10
4 ovrp cr cy4 2016-08-02 
4 ovrp pr cy5 2016-11-10
5 ovrp cr cy4 2016-08-02 
5 ovrp pr cy5 2016-11-10
5 ovrp sd dvs 2016-11-10
;
proc sort data=ONE out=SORTED(index=(ID_ORRES=(ID ORRES)));
  by ID DATE  VISIT;
run;
data TWO; 
  set SORTED;
  by ID;

  %* init;
  if first.ID then ID_DONE=0;
  if ID_DONE=0;
  ORDER=6-whichc(ORRES,'cr','pr','sd','pd','ne');
  output;

  if VISIT='cy6' then do;
    ID_DONE+1;
    return;
  end;

  NEXTOBS=_N_+1;
  set SORTED(keep=ID ORRES rename=(ID=NEXT_ID ORRES=NEXT_ORRES) ) point=NEXTOBS;
  if ID=NEXT_ID and NEXT_ORRES='pd' then do;
    ID_DONE+1;
    return;
  end;

  if last.ID then do;
    ORDER=9e9;
    output;
  end;
  keep ID TEST ORRES VISIT DATE ORDER;
run;
proc sql;
  create table THREE as 
  select ID, TEST, ORRES, VISIT, DATE
  from TWO
  group by ID
  having ORDER=max(ORDER);
quit;

 

ID TEST ORRES VISIT DATE
1 ovrp pr cy6 2016-11-10
2 ovrp sd cy4 2016-08-02
3 ovrp cr cy4 2016-08-02
4 ovrp pr cy5 2016-11-10
5 ovrp sd dvs 2016-11-10

 

 

knveraraju91
Barite | Level 11

Thank you very much for the great help. I just modified a little to get the output i need.

 

In the code :

I just changed order=9e9          to         6-whichc(ORRES,'cr','pr','sd','pd','ne');

 

if last.ID then do;
ORDER=6-whichc(ORRES,'cr','pr','sd','pd','ne');
output;
end;

 

Sorry, I should have read my specs clearly. It says "If there are no orres='pd' and visit='cy6'"  then I have to look until last obs and take best response till then(cr>pr>sd>pd>ne).

ChrisNZ
Tourmaline | Level 20

In this case you don't need the 

if last.ID 

block. 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 10 replies
  • 1139 views
  • 4 likes
  • 3 in conversation