There total 5 risk groups. But from summary analysis the output is shown for two risk groups 2 and 5. Which means riskgroup 1,3,4 are not present for gender F and agegroup 517.
Now how to populate rows for riskgroup 1, 3, 4 with values 0 for each metric?
Gender | AgeGroup | RiskGroup | Metric | Value |
F | 517 | 2 | facdoll | 25 |
F | 517 | 2 | emipdoll | 30 |
F | 517 | 2 | EMOPDOLL | 45 |
F | 517 | 2 | SGIPDOLL | 0 |
F | 517 | 5 | facdoll | 23 |
F | 517 | 5 | emipdoll | 32 |
F | 517 | 5 | EMOPDOLL | 55 |
F | 517 | 5 | SGIPDOLL | 90 |
Some hard coding involved, not very happy.
data have;
input Gender$ AgeGroup RiskGroup Metric:$10. Value;
cards;
F 517 2 facdoll 25
F 517 2 emipdoll 30
F 517 2 EMOPDOLL 45
F 517 2 SGIPDOLL 0
F 517 5 facdoll 23
F 517 5 emipdoll 32
F 517 5 EMOPDOLL 55
F 517 5 SGIPDOLL 90
;
proc sql;
select distinct quote(metric) into :metric separated by ',' from have;
quit;
data want (drop=_:);
length metric $10.;
retain riskgroup 0;
set have (rename=(riskgroup=_risk metric=_m value=_v));
do riskgroup=riskgroup+1 to _risk-1;
do metric=&metric;
value=0;
output;
end;
end;
riskgroup=_risk;
metric=_m;
value=_v;
output;
run;
proc print;run;
Haikuo
Edit to reduce some hard coding.
what if we want to insert the records for metric in the same order as that of riskgroup 2?
Facdoll
emipdoll
emopdoll
sgipdoll
The simplest way is to make a order variable for METRIC variable.
data have; input Gender$ AgeGroup RiskGroup Metric:$10. Value; cards; F 517 2 facdoll 25 F 517 2 emipdoll 30 F 517 2 EMOPDOLL 45 F 517 2 SGIPDOLL 0 F 517 5 facdoll 23 F 517 5 emipdoll 32 F 517 5 EMOPDOLL 55 F 517 5 SGIPDOLL 90 ; data x; do riskgroup=1 to 5; output; end; run; proc sql; create table temp as select * from (select distinct gender from have), (select distinct agegroup from have), (select distinct riskgroup from x), (select distinct metric from have) ; quit; proc format; invalue $ fmt 'facdoll'=1 'emipdoll '=2 'EMOPDOLL'=3 'SGIPDOLL'=4; run; proc sort data=temp;by gender agegroup riskgroup metric;run; proc sort data=have;by gender agegroup riskgroup metric;run; data want; merge temp have; by gender agegroup riskgroup metric; flag=input(metric,$fmt.); run; proc sort data=want;by gender agegroup riskgroup flag;run;
Ksharp
One more proposal :
data have;
input Gender$ AgeGroup RiskGroup Metric:$10. Value;
datalines;
F 517 2 facdoll 25
F 517 2 emipdoll 30
F 517 2 EMOPDOLL 45
F 517 2 SGIPDOLL 0
F 517 5 facdoll 23
F 517 5 emipdoll 32
F 517 5 EMOPDOLL 55
F 517 5 SGIPDOLL 90
;
data rg; do riskGroup = 1 to 5; output; end; run;
data mo; input metric$ @@; mOrder = _n_; datalines;
facdoll emipdoll EMOPDOLL SGIPDOLL
;
proc sql;
create table want as
select h1.gender, h1.ageGroup, rg.riskGroup, mo.metric, coalesce(h2.value,0) as value
from (select distinct gender, ageGroup from have as h1) cross join
rg cross join mo left join
have as h2 on h1.gender=h2.gender and h1.ageGroup=h2.ageGroup and
rg.riskGroup=h2.riskGroup and mo.metric=h2.metric
order by gender, ageGroup, riskGroup, mOrder;
drop table rg, mo;
select * from want;
quit;
PG
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.