One entry for a group of rows with nonempty variables

[Bright]

Hi,

I have a dataset like this:

ID     Proc1              Proc2         Proc3                 Proc4

10                                              03/30/2021 

10     03/31/2021

10                                                                        03/27/2021

20    03/20/2021

20                         03/20/2021    

20                                              03/20/2021 

20                                                                        03/20/2021

I want to have a table like this as the output

ID   Proc1           Proc2            Proc3               Proc4

10  03/31/2021    NA               03/30/2021      03/27/2021

20  03/20/2021   03/20/2021   03/20/2021     03/20/2021

Is there any way that I can get such an output?

Thanks!

[ballardw]

Are the variables Proc1 through Proc4 numeric (with a date format applied) or character?

Such details matter.

[Bright]

Yes they are numeric with date format.

[Bright]

Yes they are numeric with the date format.

[japelin]

how about this.

data have;
  infile datalines dsd missover;
  input ID (Proc1 Proc2 Proc3 Proc4)(:mmddyy10.);
  format Proc1-Proc4 mmddyy10.;
datalines;
10,,,03/30/2021 
10,03/31/2021
10,,,,03/27/2021
20,03/20/2021
20,,03/20/2021
20,,,03/20/2021 
20,,,,03/20/2021
;
run;

data want;
  set have;
  by id;
  array prockeep{4} _temporary_;
  array procvar{4} proc1-proc4;
  retain prockeep:;
  if first.id then do;
    do i=1 to dim(prockeep);
      prockeep{i}=.;
    end;
  end;
  do i=1 to dim(prockeep);
    if procvar{i}^=. then prockeep{i}=procvar{i};
  end;
  if last.id then do;
    do i=1 to dim(prockeep);
      procvar{i}=prockeep{i};
    end;
    output;
  end;
  drop i;
run;

Variables are numeric, so it can't be display NA.

[andreas_lds]

By using a custom format, missing values can displayed as  "n/a". I would not attach such format to a dataset, but use it in printing procs.

proc format;
   value DateNA
      . = 'N/A'
      other = [mmddyy10.]
   ;
run;

proc print data=want;
   format proc: DateNA.;
run;

[Ksharp · Accepted solution]

data have;
  infile datalines dsd missover;
  input ID (Proc1 Proc2 Proc3 Proc4)(:mmddyy10.);
  format Proc1-Proc4 mmddyy10.;
datalines;
10,,,03/30/2021 
10,03/31/2021
10,,,,03/27/2021
20,03/20/2021
20,,03/20/2021
20,,,03/20/2021 
20,,,,03/20/2021
;
run;

proc format;
   value DateNA
      . = 'N/A'
      other = [mmddyy10.]
   ;
run;

data want;
 update have(obs=0) have;
 by id;
 format proc: datena.;
 run;