topic Consolidating Rows by Creating Additional Columns in New SAS User

Consolidating Rows by Creating Additional Columns

tbs — Fri, 25 Jan 2019 23:07:09 GMT

I have a dataset of policies that sometimes have multiple IDs associated with them. Instead of having multiple rows for each policy I want to add additional columns to store that information side by side. How would I go about doing this?

Current Dataset Example:

Policy	ID	Percent
12345	12	5%
12345	15	5%
2346	23	6%
2346	24	7%
15678	30	5%

Goal Dataset Example:

Policy	ID1	Percent1	ID2	Percent2
12345	12	5%	15	5%
2346	23	6%	24	7%
15678	30	5%

Re: Consolidating Rows by Creating Additional Columns

VDD — Sat, 26 Jan 2019 00:47:32 GMT

proc transpose would be a tool of my choice here is a good link to fit your needs and help you understand how transpose works

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

Ok @tbs I will update and give you the code:

data have;
input Policy ID Percent $;
cards;
12345 12 5%
12345 15 5%
2346 23 6%
2346 24 7%
15678 30 5%
;
proc transpose data=have out=want1(drop=_name_) prefix=ID;
	by Policy notsorted;
	var id;
run;
proc sort data=want1;
	by policy;
run;
proc transpose data=have out=want2(drop=_name_) prefix=percent;
	by Policy notsorted;
	var percent;
run;
proc sort data=want2;
	by policy;
run;
data wanted;
	merge want1 want2;
	by policy;
run;

Re: Consolidating Rows by Creating Additional Columns

novinosrin — Fri, 25 Jan 2019 23:23:45 GMT

data have;
input Policy	ID	Percent :percent.;
format percent percent5.;
cards;
12345	12	5%
12345	15	5%
2346	23	6%
2346	24	7%
15678	30	5%
;

proc sql;
select max(c) into :n trimmed
from (select policy, count(id) as c from have group by policy);
quit;
%put &n;
data want;
do _n_=1  by 1 until(last.policy);
set have;
by policy notsorted;
array i(*) id1-id&n ;
array p(*)  percent1-percent&n ;
i(_n_)=id;
p(_n_)=percent;
end;
drop id percent;
run;

Re: Consolidating Rows by Creating Additional Columns

novinosrin — Fri, 25 Jan 2019 23:28:28 GMT

data have;
input Policy	ID	Percent :percent.;
format percent percent5.;
cards;
12345	12	5%
12345	15	5%
2346	23	6%
2346	24	7%
15678	30	5%
;

proc sql;
select max(c) into :n trimmed
from (select policy, count(id) as c from have group by policy);
quit;
%put &n;
proc summary nway data=have missing; 
 class policy; 
 output 
 out = want(drop=_type_ _freq_) 
 idgroup(out[&n](id percent)=) 
 ;
run;

Re: Consolidating Rows by Creating Additional Columns

Ksharp — Sat, 26 Jan 2019 10:41:50 GMT

Or try MERGE skill proposed by me ,Matt, Arthur.T .

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

data have;
input Policy	ID	Percent :percent.;
format percent percent5.;
cards;
12345	12	5%
12345	15	5%
2346	23	6%
2346	24	7%
15678	30	5%
;
data temp(index=(policy));
 set have;
 by Policy notsorted;
 if first.Policy then n=0;
 n+1;
run;
proc freq data=temp noprint;
table n/out=key;
run;
data _null_;
 set key end=last;
 if _n_=1 then call execute('data want; merge ');
 call execute(catt('temp(where=(n=',n,') rename=(id=id_',n,' percent=percent_',n,'))'));
 if last then call execute(';by Policy;drop n;run;');
run;

Re: Consolidating Rows by Creating Additional Columns

tbs — Tue, 29 Jan 2019 21:04:33 GMT

Thanks for the help! The merge skill worked perfect as I ended up having multiple variables.