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!
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;
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: