topic Re: One entry for a group of rows with nonempty variables in SAS Programming

One entry for a group of rows with nonempty variables

Bright — Thu, 01 Apr 2021 02:09:07 GMT

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

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!

Re: One entry for a group of rows with nonempty variables

ballardw — Thu, 01 Apr 2021 02:47:01 GMT

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

Such details matter.

Re: One entry for a group of rows with nonempty variables

Bright — Thu, 01 Apr 2021 02:51:02 GMT

Yes they are numeric with date format.

Re: One entry for a group of rows with nonempty variables

Bright — Thu, 01 Apr 2021 02:51:44 GMT

Yes they are numeric with the date format.

Re: One entry for a group of rows with nonempty variables

japelin — Thu, 01 Apr 2021 03:02:57 GMT

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.

Re: One entry for a group of rows with nonempty variables

andreas_lds — Thu, 01 Apr 2021 04:57:51 GMT

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;

Re: One entry for a group of rows with nonempty variables

Ksharp — Thu, 01 Apr 2021 10:59:08 GMT

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;