topic Aligning a dataset in SAS Programming

Aligning a dataset

pkopersk — Wed, 18 Sep 2019 10:22:09 GMT

Hello,

I have a dataset with the following structure:

data have;
   input data_set variable_name$    DATA_A$    DATA_B$    DATA_C$	DATA_D$ ; 
   cards; 
1 A num . . . 
1 B num . . . 
1 C char . . .
1 D num . . . 
2 A . num . . 
2 B . num . .
2 C . char . .
2 D . num . . 
3 A . . num . 
3 B . . num .
3 C . . char .
3 D . . num .  
4 A . . . num 
4 B . . . num
4 C . . . char
4 D . . . num 
;;;;;
   run;

I want to end up with this:

   data want;
   input  variable_name$    DATA_A$    DATA_B$    DATA_C$    DATA_D$ ; 
   cards; 
A num num num num 
B num num num num  
C char char char char
D num num num num num 
;;;;
   run;

Which means that I want to summarize or align my dataset to make it more compact and remove the blank spaces.

Thanks for you help

Re: Aligning a dataset

PeterClemmensen — Wed, 18 Sep 2019 10:32:00 GMT

Here is one way

proc sort data=have;
    by variable_name;
run;

data want(drop=data_set);
    update have(obs=0) have;
    by variable_name;
run;

Result:

variable_name DATA_A DATA_B DATA_C DATA_D
A   num    num    num    num
B   num    num    num    num
C   char   char   char   char
D   num    num    num    num

Re: Aligning a dataset

novinosrin — Wed, 18 Sep 2019 12:05:17 GMT

Without a SORT

data have;
   input data_set variable_name$    DATA_A$    DATA_B$    DATA_C$	DATA_D$ ; 
   cards; 
1 A num . . . 
1 B num . . . 
1 C char . . .
1 D num . . . 
2 A . num . . 
2 B . num . .
2 C . char . .
2 D . num . . 
3 A . . num . 
3 B . . num .
3 C . . char .
3 D . . num .  
4 A . . . num 
4 B . . . num
4 C . . . char
4 D . . . num 
;;;;;
   run; 

data _null_;
if _n_=1 then do;
 dcl hash H (dataset:'have(obs=0)',ordered: "A") ;
 h.definekey  ("variable_name") ;
 h.definedata ("variable_name","DATA_A", "DATA_B", "DATA_C","DATA_D") ;
 h.definedone () ;
 end;
set have end=l;
array t(*) DATA_A--DATA_D;
array u(4)$ _temporary_;
do _n_=1 to dim(t);
 u(_n_)=t(_n_);
end;
_n_=whichc(coalescec(of u(*)),of u(*));
rc=h.find();
t(_n_)=u(_n_);
h.replace();
if l;
h.output(dataset:'want');
run;