BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nikos
Fluorite | Level 6

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
1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

6 REPLIES 6
Haikuo
Onyx | Level 15

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

Nikos
Fluorite | Level 6

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

Haikuo
Onyx | Level 15

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

MikeZdeb
Rhodochrosite | Level 12

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;

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

Nikos
Fluorite | Level 6

Thank you all for your solutions

Kind regards

Nikos

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 870 views
  • 6 likes
  • 4 in conversation