DATA Step, Macro, Functions and more

Insert Records

Reply
Super Contributor
Posts: 647

Insert Records

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?

GenderAgeGroupRiskGroupMetricValue
F5172facdoll 25
F5172emipdoll  30
F5172EMOPDOLL45
F5172SGIPDOLL 0
F5175facdoll 23
F5175emipdoll  32
F5175EMOPDOLL55
F5175SGIPDOLL 90
Respected Advisor
Posts: 3,124

Re: Insert Records

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=_Smiley Happy;

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.

Super Contributor
Posts: 647

Insert Records

what if we want to insert the records for metric in the same order as that of riskgroup 2?

Facdoll

emipdoll

emopdoll

sgipdoll

Super User
Posts: 9,676

Re: Insert Records

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

Respected Advisor
Posts: 4,644

Re: Insert Records

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

PG
Ask a Question
Discussion stats
  • 4 replies
  • 275 views
  • 1 like
  • 4 in conversation