transposing multi variables

[sfffdg]

Hi ,

If I want to change the data set from A to B ,what is the way.

Data set A

Risk	Risk class	Risk plan	Key
1	ART	1	A
1	ART	3	B
1	ART	5	C
2	SCH	5	C
1	SCH	1	D
1	ART	3	E
2	SCH	3	E

B

Key	Risk1	Risk2	Risk class1	Risk class2	Risk plan1	Risk Plan2
A	1		ART		1
B	1	2	ART		3
C	1		ART	SCH	5	5
D	1		SCH		1
E	1	2	ART	SCH	3	3

the key thing is here risk is not a constant for  a max of 2 for each key,it can have any number for each key ..

for ex: key A has  1 risk value.

           key B  has 1 risk value

           key  C has 2 risk values

           key  D has 1 risk value

           key E has 2 risk values

           Key F can have 3 risk values.

I can do with arrays but I know if the risk value is max 2 or fixed  then it can be  done.but i am not sure how to achieve if the risk max number is not fixed.

Thanks in advance.

[andreas_lds]

Calling proc transpose for each variable (risk, risk_class and risk_plan) and merging the results (all by key), seems to be an easy way to transform the dataset into a report-like structure.

proc transpose data= have out=Risk(drop= _name_) prefix=Risk;
   by Key;
   var Risk;
run;

proc transpose data= have out=Risk_Class(drop= _name_) prefix=Risk_Class;
   by Key;
   var Risk_Class;
run;

proc transpose data= have out=Risk_Plan(drop= _name_) prefix=Risk_Plan;
   by Key;
   var Risk_Plan;
run;

data want;
   merge Risk Risk_Class Risk_Plan;
   by Key;
run;

[sfffdg]

Thanks a lot for the solution .

I find it is working fine except for one scenario. ( For key 'F')

excepted result:

but actual result;

how can we get rid of this problem?

[Kurt_Bremser]

Create a counter, and run a double PROC TRANSPOSE:

data have;
infile datalines dlm="09"x dsd;
input risk risk_class $ risk_plan $ key $;
datalines;
1	ART	1	A
1	ART	3	B
1	ART	5	C
2	SCH	5	C
1	SCH	1	D
1	ART	3	E
2	SCH	3	E
2	ART	1	F
;

data pretrans;
set have;
by key;
if first.key
then ct = 1;
else ct + 1;
run;

proc transpose data=pretrans out=long;
by key ct;
var risk risk_class risk_plan;
run;

proc transpose data=long out=want (drop=_name_);
by key;
var col1;
id _name_ ct;
run;

You can also run PROC REPORT on pretrans:

proc report data=pretrans;
column key ct,(risk risk_class risk_plan) n;
define key / group;
define ct / "" across;
define risk / display;
define risk_class / display;
define risk_plan / display;
define n / noprint;
run;

[sfffdg]

Thanks a lot for the solution Kurt.

But it also gives the same result as andreas_ids.

actual result:

expected result is:

[Kurt_Bremser · Accepted solution]

Then you run the double transpose without the extra counter, but by using risk as ID variable:

data have;
infile datalines dlm="09"x dsd;
input risk risk_class $ risk_plan $ key $;
datalines;
1	ART	1	A
1	ART	3	B
1	ART	5	C
2	SCH	5	C
1	SCH	1	D
1	ART	3	E
2	SCH	3	E
2	ART	1	F
;

proc transpose data=have out=long;
by key risk;
var risk risk_class risk_plan;
run;

proc transpose data=long out=want (drop=_name_);
by key;
var col1;
id _name_ risk;
run;

[Ksharp]

data have;
infile datalines dlm="09"x dsd;
input risk risk_class $ risk_plan $ key $;
datalines;
1	ART	1	A
1	ART	3	B
1	ART	5	C
2	SCH	5	C
1	SCH	1	D
1	ART	3	E
2	SCH	3	E
2	ART	1	F
;

proc sql noprint;
select max(n) into : n from (select key,count(*) as n from have group by key);
quit;
proc summary data=have;
by key;
output out=want idgroup(out[&n] (risk risk_class risk_plan)=);
run;
proc print;run;