topic Re: transposing multi variables in SAS Programming

transposing multi variables

sfffdg — Thu, 07 Oct 2021 05:10:10 GMT

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

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.

Re: transposing multi variables

andreas_lds — Thu, 07 Oct 2021 05:19:16 GMT

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;

Re: transposing multi variables

sfffdg — Thu, 07 Oct 2021 07:40:49 GMT

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?

Re: transposing multi variables

Kurt_Bremser — Thu, 07 Oct 2021 09:19:56 GMT

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;

Re: transposing multi variables

sfffdg — Thu, 07 Oct 2021 09:59:33 GMT

Thanks a lot for the solution Kurt.

But it also gives the same result as andreas_ids.

actual result:

expected result is:

Re: transposing multi variables

Kurt_Bremser — Thu, 07 Oct 2021 10:41:39 GMT

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;

Re: transposing multi variables

Ksharp — Thu, 07 Oct 2021 14:06:52 GMT

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;