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

Use @Reeza's and @Astounding's logic but create the all_combinations with the next code

proc sql;
   create table temp1 as 
      select distinct id, date_of_entry;
quit;

data all_combinations;
  set temp1;
        start_year = year(date_of_entry) - 1; 
              /* change if needed accoring to date_of_entry format */
        do year = start_year to (start_year +2);
            do qtr=1 to 4;
                 output;
        end; end;
keep id year qtr date_of_entry; /* or drop start_year */ run;

continue with code of @Astounding (sort, merge, replace missing spent by 0).

novinosrin
Tourmaline | Level 20

I'm afraid I haven't read through the thread as I am feeling very lazy. However, for your sample HAVE and WANT, and here you go:

 

 

 

data have;

input ID   Year    Qtr   Spent;

datalines;

1    2010   1       50

1    2010   2      100

1    2010   4        25

1    2011   2        100

1    2011   4        200

;

 

 

 

data want;

do until(last.id);

  do _n_=1 by 1 until(last.year);

   set have;

   by id year;

   if first.year then _qtr=qtr;

   if _n_=1 and qtr ne _n_ then

   do;

   _qtr=qtr;

   _spent=spent;

     do qtr=_n_ to qtr-1;

      spent=0;

      output;

      end;

      spent=_spent;

      output;

      _qtr=qtr;

  end;

  else if _qtr ne qtr then

  do;

    _spent=spent;

    do qtr=_qtr+1 to qtr-1;

    spent=0;

    output;

    end;

    spent=_spent;

     output;

     _qtr=qtr;

 end;

 else output;

 end;

end;

drop _:;

run;

 

 

 

Ksharp
Super User

It is very easy for SQL.

 

 

data have;
input ID   Year    Qtr   Spent ;
cards;
1    2010   1       50
1    2010   2      100 
1    2010   4        25
1    2011   2        100
1    2011   4        200
;
run;
data qtr;
 do qtr=1 to 4;
  output;
 end;
run;

proc sql;
create table want as
 select  a.*,coalesce(spent,0) as spent
  from
  (
select * from
(select distinct id from have),
(select distinct year from have),
(select distinct qtr from qtr)
) as a 
left join have as b
on a.id=b.id and a.year=b.year and a.qtr=b.qtr ;
quit;
novinosrin
Tourmaline | Level 20

@Ksharp  Thank you. It's something very intriguing to read your brilliant innovative solutions. I wish I had the drive to be keen, driven and dedicated as you are however I am too lazy to venture into much learning and applying. My bad. Thank you for making me think. Cheers!

 

Regards,

Naveen Srinivasan

Reeza
Super User

@novinosrin your participation on here would indicate the opposite 😉

 


@novinosrin wrote:

 I wish I had the drive to be keen, driven and dedicated as you are however I am too lazy to venture into much learning and applying. My bad. Thank you for making me think.


 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 19 replies
  • 2028 views
  • 6 likes
  • 6 in conversation