Help using Base SAS procedures

"Expand" a "timeseries" dataset without using Proc Expand

Accepted Solution Solved
Reply
Contributor
Posts: 68
Accepted Solution

"Expand" a "timeseries" dataset without using Proc Expand

Hi all,

I want to "expand" a dataset (without using PROC EXPAND) by adding missing observations based on TRNS_MONTH and TRNS_QTR by each ID group.

In the new added rows Colour and Weight needs to copy down their values while the AMTS (AMT_1 to AMT_4 needs to be set equal to zero.

Expansion needs to be carried up to the MAX TRNS_MONTH & TRNS_QTR as they are determined by ID

704greenow2011122011Q4

Thank you very much in advance

Nik

DATA HAVE
IDColourWeightTRNS_MONTHTRNS_QTRAMT_1AMT_2AMT_3AMT_4
811blueow2011082011Q34900000
811blueow2011092011Q304580230700
704greenow2010032010Q13340004500
704greenow2010082010Q3114800000
704greenow2010122010Q434550000-8900
704greenow2011122011Q423300002145
38redow2010122010Q43300000
38redow2011012011Q1013000560
DATA WANT
IDColourWeightTRNS_MONTHTRNS_QTRAMT_1AMT_2AMT_3AMT_4
811blueow2011082011Q34900000
811blueow2011092011Q304580230700
811blueow2011102011Q40000
811blueow2011112011Q40000
811blueow2011122011Q40000
704greenow2010032010Q13340004500
704greenow2010042010Q20000
704greenow2010052010Q20000
704greenow2010062010Q20000
704greenow2010072010Q30000
704greenow2010082010Q3114800000
704greenow2010092010Q30000
704greenow2010102010Q40000
704greenow2010112010Q40000
704greenow2010122010Q434550000-8900
704greenow2011012011Q10000
704greenow2011022011Q10000
704greenow2011032011Q10000
704greenow2011042011Q20000
704greenow2011052011Q20000
704greenow2011062011Q20000
704greenow2011072011Q30000
704greenow2011082011Q30000
704greenow2011092011Q30000
704greenow2011102011Q40000
704greenow2011112011Q40000
704greenow2011122011Q423300002145
38redow2010122010Q43300000
38redow2011012011Q1013000560
38redow2011022011Q10000
38redow2011032011Q10000
38redow2011042011Q20000
38redow2011052011Q20000
38redow2011062011Q20000
38redow2011072011Q30000
38redow2011082011Q30000
38redow2011092011Q30000
38redow2011102011Q40000
38redow2011112011Q40000
38redow2011122011Q40000

Accepted Solutions
Solution
‎07-02-2012 08:26 PM
Respected Advisor
Posts: 3,156

Re: "Expand" a "timeseries" dataset without using Proc Expand

Hi,

That would make the coding simpler, as DOWs may not be needed. However, as lazy as I am, I will just make some minor change to my current code:

DATA HAVE;

input ID$    Colour$    Weight$    TRNS_MONTH :yymmn6.    TRNS_QTR :yyq6.    AMT_1    AMT_2    AMT_3    AMT_4;

format trns_month yymmn6.

       trns_qtr yyq6.;

cards;

811    blue    ow    201108    2011Q3    4900    0    0    0

811    blue    ow    201109    2011Q3    0    4580    230    700

704    green    ow    201003    2010Q1    33400    0    450    0

704    green    ow    201008    2010Q3    11    48000    0    0

704    green    ow    201012    2010Q4    345    5000    0    -8900

704    green    ow    201112    2011Q4    23300    0    0    2145

38    red    ow    201012    2010Q4    3300    0    0    0

38    red    ow    201101    2011Q1    0    1300    0    560

;

proc sql;

select max(trns_month) into :mtm from have;

quit;

data want ;

format tm yymmn6.;

do until (last.id);

set have;

by id notsorted;

if first.id then _st=trns_month;

if last.id then do;

       _ld=trns_month;

end;

end;

_i=0; tm =0;

do until (last.id);

   set have;

    by id notsorted;

    _amt1=amt_1;

    _amt2=amt_2;

    _amt3=amt_3;

    _amt4=amt_4;

      do _i=_i by 1 while (tm < &mtm);

          tm=intnx('month',_st,_i,'b');

          tq=put(tm,yyq6.);

         if trns_month=tm  then do;

                 amt_1=_amt1;

                 amt_2=_amt2;

                 amt_3=_amt3;

                 amt_4=_amt4;

                  output;

                  if tm ne _ld  then do;

                   _i+1;

                   leave;

                  end;

         end;

         else do;

             amt_1=0;

             amt_2=0;

             amt_3=0;

             amt_4=0;

             output;

         end;

       end;

end;

rename tm=trns_month tq=trns_qtr;

drop trns_qtr trns_month _:;

run;

Haikuo

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: "Expand" a "timeseries" dataset without using Proc Expand

Well, you will probably want to pay for SAS/ETS, for not doing the following:

DATA HAVE;

input ID$    Colour$    Weight$    TRNS_MONTH :yymmn6.    TRNS_QTR :yyq6.    AMT_1    AMT_2    AMT_3    AMT_4;

format trns_month yymmn6.

       trns_qtr yyq6.;

cards;

811    blue    ow    201108    2011Q3    4900    0    0    0

811    blue    ow    201109    2011Q3    0    4580    230    700

704    green    ow    201003    2010Q1    33400    0    450    0

704    green    ow    201008    2010Q3    11    48000    0    0

704    green    ow    201012    2010Q4    345    5000    0    -8900

704    green    ow    201112    2011Q4    23300    0    0    2145

38    red    ow    201012    2010Q4    3300    0    0    0

38    red    ow    201101    2011Q1    0    1300    0    560

;

data want ;

format tm yymmn6.;

do until (last.id);

set have;

by id notsorted;

if first.id then _st=trns_month;

if last.id then do;

       _ed=intnx('month',intnx('year',trns_month,1,'b'),-1,'b');

       _ld=trns_month;

end;

end;

_i=0; tm =0;

do until (last.id);

   set have;

    by id notsorted;

    _amt1=amt_1;

    _amt2=amt_2;

    _amt3=amt_3;

    _amt4=amt_4;

      do _i=_i by 1 while (tm < _ed);

          tm=intnx('month',_st,_i,'b');

          tq=put(tm,yyq6.);

         if trns_month=tm  then do;

                 amt_1=_amt1;

                 amt_2=_amt2;

                 amt_3=_amt3;

                 amt_4=_amt4;

                  output;

                  if tm ne _ld  then do;

                   _i+1;

                   leave;

                  end;

         end;

         else do;

             amt_1=0;

             amt_2=0;

             amt_3=0;

             amt_4=0;

             output;

         end;

       end;

end;

rename tm=trns_month tq=trns_qtr;

drop trns_qtr trns_month _:;

run;

Good Luck,

Haikuo

Contributor
Posts: 68

Re: "Expand" a "timeseries" dataset without using Proc Expand

Hi Hai Kuo,

Thank you for your code. I might have not been very clear but I want each and every ID group to "expand" up and including the Maximum TRNS_MONTH of the dataset, in which case is 201112.

Thank you again.

Nick

Solution
‎07-02-2012 08:26 PM
Respected Advisor
Posts: 3,156

Re: "Expand" a "timeseries" dataset without using Proc Expand

Hi,

That would make the coding simpler, as DOWs may not be needed. However, as lazy as I am, I will just make some minor change to my current code:

DATA HAVE;

input ID$    Colour$    Weight$    TRNS_MONTH :yymmn6.    TRNS_QTR :yyq6.    AMT_1    AMT_2    AMT_3    AMT_4;

format trns_month yymmn6.

       trns_qtr yyq6.;

cards;

811    blue    ow    201108    2011Q3    4900    0    0    0

811    blue    ow    201109    2011Q3    0    4580    230    700

704    green    ow    201003    2010Q1    33400    0    450    0

704    green    ow    201008    2010Q3    11    48000    0    0

704    green    ow    201012    2010Q4    345    5000    0    -8900

704    green    ow    201112    2011Q4    23300    0    0    2145

38    red    ow    201012    2010Q4    3300    0    0    0

38    red    ow    201101    2011Q1    0    1300    0    560

;

proc sql;

select max(trns_month) into :mtm from have;

quit;

data want ;

format tm yymmn6.;

do until (last.id);

set have;

by id notsorted;

if first.id then _st=trns_month;

if last.id then do;

       _ld=trns_month;

end;

end;

_i=0; tm =0;

do until (last.id);

   set have;

    by id notsorted;

    _amt1=amt_1;

    _amt2=amt_2;

    _amt3=amt_3;

    _amt4=amt_4;

      do _i=_i by 1 while (tm < &mtm);

          tm=intnx('month',_st,_i,'b');

          tq=put(tm,yyq6.);

         if trns_month=tm  then do;

                 amt_1=_amt1;

                 amt_2=_amt2;

                 amt_3=_amt3;

                 amt_4=_amt4;

                  output;

                  if tm ne _ld  then do;

                   _i+1;

                   leave;

                  end;

         end;

         else do;

             amt_1=0;

             amt_2=0;

             amt_3=0;

             amt_4=0;

             output;

         end;

       end;

end;

rename tm=trns_month tq=trns_qtr;

drop trns_qtr trns_month _:;

run;

Haikuo

Valued Guide
Posts: 765

Re: "Expand" a "timeseries" dataset without using Proc Expand

hi ...another idea (if you don't mind the IDs in ascending order) ...

proc sort data=have out=temp;

by id trns_month;

run;

data want (drop=y m dt start);

set temp (rename=(trns_month=dt));

by id;

if first.id then do;

   amt_1=0; amt_2=0; amt_3=0; amt_4=0;

   do y=year(dt) to 2011;

      if y eq year(dt) then start = month(dt);

      else start = 1;

      do m=start to 12;

         trns_month = mdy(m,1,y);

         trns_qtr = trns_month;

         output;

      end;

   end;

end;

format trns_month yymmn6. trns_qtr yyq6.;

run;

    

data want;

update want temp;

by id trns_month;

run;

Super User
Posts: 10,041

Re: "Expand" a "timeseries" dataset without using Proc Expand

DATA HAVE;
input ID$    Colour$    Weight$    TRNS_MONTH :yymmn6.    TRNS_QTR :yyq6.    AMT_1    AMT_2    AMT_3    AMT_4;
format trns_month yymmn6.       trns_qtr yyq6.;
cards;
811    blue    ow    201108    2011Q3    4900    0    0    0
811    blue    ow    201109    2011Q3    0    4580    230    700
704    green    ow    201003    2010Q1    33400    0    450    0
704    green    ow    201008    2010Q3    11    48000    0    0
704    green    ow    201012    2010Q4    345    5000    0    -8900
704    green    ow    201112    2011Q4    23300    0    0    2145
38    red    ow    201012    2010Q4    3300    0    0    0
38    red    ow    201101    2011Q1    0    1300    0    560
;
run;
data want(drop=i _:);
 merge have have(firstobs=2 keep=ID TRNS_MONTH rename=(ID=_ID TRNS_MONTH=_TRNS_MONTH));
 output;
 if id=_id then do;
                  do i=1 to intck('month',TRNS_MONTH,_TRNS_MONTH)-1;
                   TRNS_MONTH=intnx('month',TRNS_MONTH,1);
                   TRNS_QTR=TRNS_MONTH;
                   amt_1=0;
                   amt_2=0;
                   amt_3=0;
                   amt_4=0; 
                   output;
                  end;
                end;
  else do;
         do i=1 to intck('month',TRNS_MONTH,'01dec2011'd);
                   TRNS_MONTH=intnx('month',TRNS_MONTH,1);
                   TRNS_QTR=TRNS_MONTH;
                   amt_1=0;
                   amt_2=0;
                   amt_3=0;
                   amt_4=0; 
                   output;
                  end;
        end;
run;


Ksharp

Contributor
Posts: 68

Re: "Expand" a "timeseries" dataset without using Proc Expand

Thank you all for your solutions

Kind regards

Nikos

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 265 views
  • 6 likes
  • 4 in conversation