topic How to transpose array and assign column names in SAS Programming

How to transpose array and assign column names

Bikila — Wed, 20 Nov 2019 00:29:52 GMT

Hello,

Can anyone advice me how to transpose the following data?

I have this data:

data have;
input Claim_ID CODE $5;
datelines;
10001       90651 
10001       90670 
10001       90680 
10001       90698 
10001       91170 
10002       92405 
10002       90710 
10003       90715
;

I want to have this data;

Claim_ID  Code_1      Code_2     Code_3    Code_4     Code_5

10001     90651      90670    90680     90698    91170  

10002     92405     90710          

10003     90715
;

The number of Code can be more than five per claim but I want to consider the maximum number of distinct Code by claim as my columns.

Thank you in advance!

Re: How to transpose array and assign column names

novinosrin — Wed, 20 Nov 2019 00:55:36 GMT

Hi @Bikila Are you asking for this?


data have;
input Claim_ID CODE :$5.;
datalines;
10001       90651 
10001       90670 
10001       90680 
10001       90698 
10001       91170 
10002       92405 
10002       90710 
10003       90715
;
proc transpose data=have out=want(drop=_name_) prefix=CODE_;
by claim_id;
var code;
run;

Re: How to transpose array and assign column names

ed_sas_member — Wed, 20 Nov 2019 10:18:32 GMT

Hi @Bikila ,

Another way to achieve this using an array (alternatively to the solution posted by @novinosrin )


/* Put the max number of code_ variables in a macro variable &max_obs -> here it is 5 */

proc sql noprint;
	select max(frequency) into: max_obs from (select count(Claim_ID) as frequency from have group by Claim_ID);
run;

data want;
	set have;
	
	array code_(&max_obs) $;
	
	by claim_id;
	retain code_;
	
 	if first.claim_id then do;
 		counter=0;
 		call missing (of code_(*));
 	end;
 	counter+1;

	do i=1 to dim(code_);
		code_(counter) = code;
	end;
	
	if last.claim_ID then output;
	drop i code counter;
run;

Re: How to transpose array and assign column names

Bikila — Tue, 07 Jan 2020 00:40:02 GMT

Thank you! This is exactly how I wanted it.