Hi,
I have below data from my database and sorted all columns except Col4 column. so i have 4 different group by sections
Proc sort data=report_tran;
BY Col1 Col2 Col3
Col1 Col2 Col3 Col4
==============================
A03 R1 2012-04 4
A03 R1 2012-08 4
A03 R1 2012-09 3
A03 R1 2012-11 1
A03 R2 2012-04 5
A03 R2 2012-06 2
A03 R2 2012-08 2
A03 R2 2012-09 3
A03 R2 2012-10 .
A03 R2 2012-11 2
A03 R2 2012-04 2
A03 R2 2012-06 2
A03 R2 2012-07 3
A03 R2 2012-08 1
A03 R2 2012-09 1
A03 R2 2012-10 1
A03 R2 2012-11 7
A03 R3 2012-04 2
A03 R3 2012-08 4
A03 R3 2012-09 1
A03 R3 2012-11 3
But i have selected stored process "Col3" input prompt values are 2012-01, 2012-02, 2012-03 to 2012-11 and 2012-12
I have to insert the duplicated records in each group by section with Col4 is empty for remaining input selected Col3 values
ex for first group by section:
Col1 COL2 COL3 COL4
==============================
A03 R1 2012-04 4
A03 R1 2012-08 4
A03 R1 2012-09 3
A03 R1 2012-11 1
A03 R1 2012-01 .
A03 R1 2012-02 .
A03 R1 2012-03 .
A03 R1 2012-05 .
A03 R1 2012-06 .
A03 R1 2012-07 .
A03 R1 2012-10 .
A03 R1 2012-12 .
could you please help me to sort-out this issue
Your input and output apperas messy and thus is difficult to understand .Can you please decrease size of your example and then paste to undeerstand better your query
I am not really sure what you want, and like others already pointed out, your input data does not make sense (R2 part), esp. after you have sorted it. But assume what you want is to insert all of the missing month with missing col4 and you have ETS:
data have (drop=_:);
input Col1$ Col2$ _Col3 $ Col4;
col3=input(compress(_col3,'-'),yymmn6.);
format col3 date9.;
cards;
A03 R1 2012-04 4
A03 R1 2012-08 4
A03 R1 2012-09 3
A03 R1 2012-11 1
;
proc timeseries data=have out=want ;
id col3 interval=month start="01jan2012"d end="01dec2012"d ;
var col4;
by col1 col2;
run;
proc print;run;
Haikuo
Update: and if want to keep your format, run this proc format first:
proc format;
picture _yf
low-high='%Y-%0m ' (datatype=date);
run;
data have (drop=_:);
input Col1$ Col2$ _Col3 $ Col4;
col3=input(compress(_col3,'-'),yymmn6.);
format col3 _yf.;
cards;
A03 R1 2012-04 4
A03 R1 2012-08 4
A03 R1 2012-09 3
A03 R1 2012-11 1
;
And since you mention base in your title, you probably don't have ETS, then here is a data step approach:
data want (drop=_:);
do _n_=1 by 1 until (last.col2);
set have;
by col1 col2;
if _n_=1 then do;
_col3=intnx('year',col3,0,'b');
_col3e=intnx('year',col3,0,'e');
end;
_col4=col4;
_col3_1=col3;
do while (_col3<_col3_1);
col3=_col3;
call missing (col4);
output;
_col3=intnx('month',_col3,1,'b');
end;
col3=_col3;
col4=_col4;
output;
_col3=intnx('month',_col3,1,'b');
retain _col3;
if last.col2 then
do until (_col3>_col3e);
col3=_col3;
call missing (col4);
output;
_col3=intnx('month',_col3,1,'b');
end;
end;
run;
proc print;run;
Haikuo
data date; do date='1mar2012'd, '01aug2012'd, '01sep2012'd, '01nov2012'd; output; end; run; data have (drop=_:); input Col1$ Col2$ _Col3 $ Col4; col3=input(compress(_col3,'-'),yymmn6.); format col3 yymmd7.; cards; A03 R1 2012-04 4 A03 R1 2012-08 4 A03 R1 2012-09 3 A03 R1 2012-11 1 A03 R2 2012-04 2 A03 R2 2012-06 2 A03 R2 2012-07 3 A03 R2 2012-08 1 A03 R2 2012-09 1 A03 R2 2012-10 1 A03 R2 2012-11 7 ; run; proc sql; create table temp as select * from (select distinct col1 from have), (select distinct col2 from have), (select distinct col3 from (select col3 from have union select date from date)); quit; proc sort data=have;by col1 col2 col3;run; proc sort data=temp;by col1 col2 col3;run; data want; merge have temp; by col1 col2 col3; run;
Ksharp
Nice SQL approach! And SQL is indeed very powerful! However, I may have misunderstood OP's intention, I think your code needs some minor tweak to really reflect what OP wanted:
proc sql;
create table temp as
select *
from (select distinct col1 from have),
(select distinct col2 from have),
(select distinct date as col3 from date);
quit;
proc sort data=have;by col1 col2 col3;run;
proc sort data=temp;by col1 col2 col3;run;
data want;
merge have temp (in=a);
by col1 col2 col3;
if a;
run;
Regards,
Haikuo
"I have to insert the duplicated records in each group by section with Col4 is empty for remaining input selected Col3 values"
OP said he want to insert some obs, Maybe that is to say OP want keep all of them including original obs .
Ksharp
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: