If missing append a dummy record

[SASPhile]

reg prod A B C
00 A 20 25 30
00 B 20 25 30
00 C 20 25 30
56 A 40 . 35
56 C 20 . 35

The above data shows that for regions 00 and 56 there should ne three products A,B,C. But region 56 has product B missing.It has only A and C. also the data shows variables A B C (products) and their count in each region.

Now since B is missing from region 56, how to add a dummy record with product B such that the data will be as shown below

reg prod A B C
00 A 20 25 30
00 B 20 25 30
00 C 20 25 30
56 A 40 1 35
56 B 40 1 35
56 C 40 1 35

[SPR]

Hello SASPhile,

It is a solution. However, you do not specify if you are interested in case when 2 products have missing values or you have more then 3 products. Anyway this is it:
[pre]
data i;
input reg prod $ A B C;
datalines;
00 A 20 25 30
00 B 20 25 30
00 C 20 25 30
56 A 40 . 35
56 C 20 . 35
run;
data t;
set i;
fa=0; fb=0; fc=0;
if A = . then do; A=1; fa=1; end;
if B = . then do; B=1; fb=1; end;
if C = . then do; C=1; fc=1; end;
where A=. or B=. or C=.;
run;
data t1;
set t;
if fa=1 then prod="A";
if fb=1 then prod="B";
if fc=1 then prod="C";
drop fa fb fc;
run;
proc sort data=t1 nodupkey;
by reg prod;
run;
data r;
set i t1;
if A = . then A=1;
if B = . then B=1;
if C = . then C=1;
run;
proc sort data=r;
by reg prod;
run;
[/pre]
Sincerely,
SPR

[SASPhile]

SPR,
There could be more than one prod missing.

[SASPhile]

And there will be 9 prods

[SPR]

Hello,

This is a second attempt:
[pre]
data i;
input reg prod $ A B C D E;
datalines;
00 A 20 25 30 10 11 12
00 B 20 25 30 60 70 80
00 C 20 25 30 25 45 23
00 D 20 25 30 56 12 15
00 E 20 25 30 65 11 22
56 A 11 . 35 . . .
56 C 11 . 35 . . .
run;
proc SQL;
select COUNT(distinct NAME) into :n from SASHELP.VCOLUMN
where LIBNAME="WORK" and MEMNAME="I" and UPCASE(NAME) not in ("REG","PROD");
%let n=%TRIM(&n);
select distinct NAME into :n1-:n&n from SASHELP.VCOLUMN
where LIBNAME="WORK" and MEMNAME="I" and UPCASE(NAME) not in ("REG","PROD");
quit;
%put n=&n n1=&n1 n&n=&&n&n;
/***/;
%macro a;
data t;
set i;
%do i=1 %to &n;
if &&n&i = . then do; &&n&i=1; prod="&&n&i"; output; end;
%end;
if &&n1=.
%do i=2 %to &n;
or &&n&i=.
%end;
;
run;
proc sort data=t nodupkey;
by reg prod;
run;
data r;
set i t;
%do i=1 %to &n;
if &&n&i = . then do; &&n&i=1; end;
%end;
run;
proc sort data=r;
by reg prod;
run;
%mend a;
%a
[/pre]
SPR

[SASPhile]

Hi,
Some of the products have space in them like "ORTHO TRI-CYCLEN LO".This is giving an error when &&n&i resolves to this value as this value is a column name.
is there a turn around fir this>

[SASPhile]

thanks!

[Ksharp]

[pre]
data temp;
input reg $ prod $ A B C;
datalines;
00 A 20 25 30
00 B 20 25 30
00 C 20 25 30
56 A 40 . 35
56 C 20 . 35
;
run;
proc stdize data=temp missing=1 reponly out=want;
run;
[/pre]



Ksharp