topic Re: Sort variables within a row / create new variables from column names in SAS Programming

Sort variables within a row / create new variables from column names

kz_ — Mon, 22 Aug 2022 17:50:01 GMT

I have three date columns as shown in the 'have' table below. I would like to sort them and create a type variable that is the name of the date column (see the 'want' table).

In some cases, two dates may be equal in which case, they will only be counted as one date when sorting them, but both names should be included in the 'type' (see row 2 of 'want').

I came up with a very clunky way to do this with if/then statements, but I am wondering if there is a better way.

data have;
   input A :yymmdd10. B :yymmdd10. C :yymmdd10. ; 
   format A B C yymmdd10.;
   infile datalines delimiter=','; 
   datalines;                      
2019/01/01, 2018/03/13, . 
2017/02/02, . , 2017/02/02
2018/11/18, 2018/08/01, 2021/07/02
;
run;


data want; 
	input date1 :yymmdd10. date2 :yymmdd10. date3 :yymmdd10. type1 $ type2 $ type3 $;
	format date1 date2 date3 yymmdd10.; 
	infile datalines delimiter=',';
	datalines; 
2018/03/13, 2019/01/01, ., B, A, . 
2017/2/2, . , . , A/B, . , .
2018/08/01, 2018/11/18, 2021/07/02, B, A, C
;
run;

Re: Sort variables within a row / create new variables from column names

Tom — Mon, 22 Aug 2022 18:14:42 GMT

Your second output seems wrong.

The original variables where A and C , not A and B.

data have;
  input (A B C) (:yymmdd.);
  format A B C yymmdd10.;
datalines;                      
2019/01/01  2018/03/13  . 
2017/02/02  .           2017/02/02
2018/11/18  2018/08/01  2021/07/02
;


data expect; 
  input (date1-date3) (:yymmdd.) (type1-type3) (:$100.);
  format date1-date3 yymmdd10.; 
datalines; 
2018/03/13  2019/01/01  .           B    A  . 
2017/2/2    .           .           A/B  .  .
2018/08/01  2018/11/18  2021/07/02  B    A  C
;

data step1;
  row+1;
  set have;
run;

proc transpose data=step1 out=step2 ;
  by row;
  var a b c ;
run;

proc sort data=step2;
  by row col1;
run;

data want;
  set step2;
  by row col1;
  where not missing(col1);
  array date [3];
  array type [3] $100 ;
  retain date: type: ;
  format date: yymmdd10.;
  if first.row then call missing(of index date[*] type[*]);
  index + first.col1;
  date[index]=col1;
  type[index]=catx('/',type[index],_name_);
  if last.row then output;
  keep row date: type:;
run;

proc print data=want;
run;
 
proc compare data=want compare=expect;
run;

The COMPARE Procedure
Comparison of WORK.WANT with WORK.EXPECT
(Method=EXACT)

Value Comparison Results for Variables

__________________________________________________________
           ||  Base Value           Compare Value
       Obs ||  type1                 type1
 ________  ||  ___________________+  ___________________+
           ||
        2  ||  A/C                   A/B
__________________________________________________________

Re: Sort variables within a row / create new variables from column names

data_null__ — Mon, 22 Aug 2022 19:14:22 GMT

Here is a method that is the same but different. 😀

data have;
   infile datalines delimiter=','; 
   id + 1;
   input A :yymmdd10. B :yymmdd10. C :yymmdd10. ; 
   format A B C yymmdd10.;
   datalines;                      
2019/01/01, 2018/03/13, . 
2017/02/02, . , 2017/02/02
2018/11/18, 2018/08/01, 2021/07/02
;
run;
proc print;
   run;
proc transpose data=have out=tall(where=(not missing(col1)));
   by id;
   run;
proc sort;
   by id col1;
   run;
data tall2;
   length type $8;
   do until(last.col1);
      set tall; by id col1;
      date = col1;
      type = catx('/',type,_name_);
      end;
   format date yymmdd10.;
   run;
proc print;
   run;
proc summary data=tall2 nway;
   class id;
   output out=want(drop=_type_) idgroup(out[3](date type)=);
   run;
proc print;
   run;

Re: Sort variables within a row / create new variables from column names

kz_ — Mon, 22 Aug 2022 23:13:37 GMT

Thank you! That's exactly what I needed.

Re: Sort variables within a row / create new variables from column names

kz_ — Mon, 22 Aug 2022 23:13:58 GMT

Thank you! This works too.