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.
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;
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;
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?
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;
Thanks a lot for the solution Kurt.
But it also gives the same result as andreas_ids.
actual result:
expected result is:
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.