DATA Step, Macro, Functions and more

BASE SAS

Reply
Frequent Contributor
Posts: 115

BASE SAS

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

Frequent Contributor
Posts: 138

Re: BASE SAS

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

Respected Advisor
Posts: 3,124

Re: BASE SAS

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

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

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

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

Super User
Posts: 9,676

Re: BASE SAS

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

Respected Advisor
Posts: 3,124

Re: BASE SAS

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

Super User
Posts: 9,676

Re: BASE SAS

"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

Ask a Question
Discussion stats
  • 5 replies
  • 413 views
  • 0 likes
  • 4 in conversation