BookmarkSubscribeRSS Feed
sunilreddy
Fluorite | Level 6

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

5 REPLIES 5
manojinpec
Obsidian | Level 7

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

Haikuo
Onyx | Level 15

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

Ksharp
Super User
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

Haikuo
Onyx | Level 15

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

Ksharp
Super User

"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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1009 views
  • 0 likes
  • 4 in conversation