sas query

[rakeshvvv]

I have a dataset with six variables ID, TEST, VISIT, DATE, DATE1, DIFF.

I would like to create output in such a way that there should only one record per id and new variable to be created to have the additional information. Below is the sample data.

100030002                          CYCLE 2 DAY 1    2014-10-15          .

100030002                          CYCLE 3 DAY 1    2014-11-05          .

100030002           APTT      CYCLE 4 DAY 1    2014-12-03          2014-11-26          7

100030002           INR         CYCLE 4 DAY 1    2014-12-03          2014-11-26          7

100030002           APTT      CYCLE 5 DAY 1    2014-12-29          2014-12-24          5

100030002           INR         CYCLE 5 DAY 1    2014-12-29          2014-12-24          5

The output should have two variables ID and NEW. The variable NEW should have the following value highlighted in green and YELLOW. Brief logic for new variable.

green: As the test was missing for first two records

YELLOW: Individual Test performed(aptt, inr) performed(diff) days from first date(date),seconddate(date1)

INR AND APTT not performed: CYCLE 2 DAY 1, CYCLE 3  DAY 1 ;APTT: CYCLE 4 DAY 1 Performed 7 days from First  dose(Lab date 2014-12-03, First dose date 2014-11-26),  INR: CYCLE 4 DAY 1 Performed 7 days from First  dose(Lab date 2014-12-03, First dose date 2014-11-26), APTT: CYCLE 5 DAY 1 Performed 5 days from First  dose(Lab date 2014-12-29, First dose date 2014-12-24), INR: CYCLE 5 DAY 1 Performed 5 days from First  dose(Lab date 2014-12-29, First dose date 2014-12-24)

Thanks

Rakesh

[RW9 · Accepted solution]

This will give you one record per id, with result as a list of visits separated by commas where data is missing.

data want;

     set have;

     length result $200;

     by id;

     retain result;

     if first.id then result="";

     if test="" then result=catx(",",result,visit);    

     if last.id then output;

run;

[Steelers_In_DC]

I got you started, does this suit your purposes?

data have;

infile cards dsd;

length id visit $25.;

informat date date1 yymmdd10.;

format date date1 yymmdd10.;

input id $ test $ visit $ date date1 diff;

cards;

100030002,,CYCLE 2 DAY 1,2014-10-15,,

100030002,,CYCLE 3 DAY 1,2014-11-05,,

100030002,APTT,CYCLE 4 DAY 1,2014-12-03,2014-11-26,7

100030002,INR,CYCLE 4 DAY 1,2014-12-03,2014-11-26,7

100030002,APTT,CYCLE 5 DAY 1,2014-12-29,2014-12-24,5

100030002,INR,CYCLE 5 DAY 1,2014-12-29,2014-12-24,5

;

run;

data want(keep=id new);

format new $225.;

set have;

if missing(test) then new = 'INR AND APPT not performed '|| visit;

/*if not missing(test) then new = etc;*/

run;

[LinusH]

So what is your question?

If this is an order you need cash upfront.