BookmarkSubscribeRSS Feed
kumarK
Quartz | Level 8

Hi guys,

 

I have these macro varibles

 

 

  weekly Bi-week Month   Daily Count Buffer
Counter 3 2 2   50 1000
Total 10 24 26      

 

i want one  framework like below, so if i change the total global variable  numbers automaticall refresh the below table.

 

please note weekly varible intialize to starting number everyweekk and bi weely after 3rd week. basicaly decrease he numbers to required ciunter every day. Remianing required column is just subrtaction from Dialy count(50)  - Total  and from buffer is ubraction from remaining required count. Thanks

 

    weekly Bi-week Month   Daily Count Buffer
  Counter 3 2 2   50 1000
  Total 10 24 26      
               
Week Day weekly Bi-week Month Total from Buffer Grand Total
Week 1 day 1 start 10 24 26      
  day 1 Count 3 2 2 7 43 50
  day 2 start 7 22 24      
  day 2 Count 3 2 2 7 43 50
  day 3 start 4 20 22      
  day 3 Count 3 2 2 7 43 50
  day 4 start 1 18 20      
  day 4 Count 1 2 2 5 45 50
  day 5 start 0 16 18      
  day 5 Count 0 2 2 4 46 50
Week 2 day 1 start 10 14 16      
  day 1 Count 3 2 2 7 43 50
  day 2 start 7 12 14      
  day 2 Count 3 2 2 7 43 50
  day 3 start 4 10 12      
  day 3 Count 3 2 2 7 43 50
  day 4 start 1 8 10      
  day 4 Count 1 2 2 5 45 50
  day 5 start 0 6 8      
  day 5 Count 0 2 2 4 46 50
Week 3 day 1 start 10 24 6      
  day 1 Count 3 2 2 7 43 50
  day 2 start 7 22 4      
  day 2 Count 3 2 2 7 43 50
  day 3 start 4 20 2      
  day 3 Count 3 2 2 7 43 50
  day 4 start 1 18 0      
  day 4 Count 1 2 0 3 47 50
  day 5 start 0 16 0      
  day 5 Count 0 2 0 2 48 50
Week 4 day 1 start 10 14 0      
  day 1 Count 3 2 0 5 45 50
  day 2 start 7 12 0      
  day 2 Count 3 2 0 5 45 50
  day 3 start 4 10 0      
  day 3 Count 3 2 0 5 45 50
  day 4 start 1 8 0      
  day 4 Count 1 2 0 3 47 50
  day 5 start 0 6 0      
  day 5 Count 0 2 0 2 48 50
9 REPLIES 9
art297
Opal | Level 21

To provide you with an answer you have to explain at least a couple of things:

 

1. why does monthly jump to 24 (rather than 26) on day 2 of week 1?

2, what is the rationale of remaining required

3. are all of your table's number consistent with what they should be?

 

Art, CEO, AnalystFinder.com

 

kumarK
Quartz | Level 8

@art297 Thanks for the response. i have edited my question the number is typo error and i have mentioned remianing required column. yes these numbers will consistent. let me know if you require more details.

ArtC
Rhodochrosite | Level 12

This is not too elegant but it solves your stated problem, i think.  Depending on the actual data you would need to add another loop for months or BY group processing if the data has one obs per month.

%let week_c = 3;
%let week_t = 6;
%let bweek_c=2;
%let bweek_t=12;
%let mon_c=2;
%let mon_t=26;
%let daily_c=50;
%let buffer=1000;

data want;
month = &mon_t+&mon_c;
frombuffer=&buffer;
do week = 1 to 4;
   do day = 1 to 5;
      if day=1 then weekly=&week_t;
      else weekly = max(0,weekly-&week_c);
      if day=1 and mod(week,2)=1 then biweek=&bweek_t;
      else biweek = max(0,biweek-&bweek_c);
      month = max(0,month-&mon_c);
      total=sum(weekly,biweek,month);
      remaining=&daily_c-total;
      FromBuffer=Frombuffer-remaining;
      output want;
   end;
end;
run;
proc print data=want;
var week day weekly biweek month total remaining frombuffer;
run;
lakshmi_74
Quartz | Level 8

The final report you can add code for the continuation of

proc format;
value week
1='week1' 2='week2' 3='week3' 4='week4';
value day_change
1='day1' 2='day2' 3='day3' 4='day4' 5='day5';
run;

proc report data=want;
columns week day weekly biweek month total remaining frombuffer;
define week/group format= week.;
define day/group format=day_change.;
run;

kumarK
Quartz | Level 8

Thanks @ArtC.. sorry i have edited my question with wanted output. can you please check

gamotte
Rhodochrosite | Level 12

Hello,

 

data want;
    keep week day 
         weekly biweekly month Total Remain Buffer;
    format weekly biweekly month best.;

    /* Parameters */
    array tot(3) (6 12 26);
    array cnt(3) (3 2 2);
    array init(3) (5 10 20); /* Number of days before reinitialization */
    Buffer=1000;
    Daily=50;

    array fr(3) weekly--month;    
   
    do nday=1 to 20;
	week=ceil(nday/5);
	day=ifn(mod(nday,5),mod(nday,5),5);
       do i=1 to 3;
            fr(i)=max(tot(i)-mod(nday-1,init(i))*cnt(i),0); 
        end;
        Total=sum(of fr(*));
        Remain=Daily-Total;
        Buffer=Buffer-Remain;
        output;
    end;    
run;
kumarK
Quartz | Level 8

Thanks @gamotte.. i have edited my question. can you please check if you dont mind

gamotte
Rhodochrosite | Level 12
data want;
    keep week day 
         counts1-counts3 weekly biweekly month Total Remain Buffer;
    format weekly biweekly month best.;

    /* Parameters */
    array tot(3) (10 24 26);
    array cnt(3) (3 2 2);
    array init(3) (5 10 20); /* Number of days before reinitialization */
    Buffer=1000;
    Daily=50;

    array fr(3) weekly--month;
	array counts(3);
   
    do nday=1 to 20;
	week=ceil(nday/5);
	day=ifn(mod(nday,5),mod(nday,5),5);
       do i=1 to 3;
            fr(i)=max(tot(i)-mod(nday-1,init(i))*cnt(i),0);
			counts(i)=min(cnt(i),fr(i));
        end;
        Total=sum(of counts(*));
        Remain=Daily-Total;
        Buffer=Buffer-Remain;
        output;
    end;    
run;
kumarK
Quartz | Level 8

Thanks @gamotte . if suppose instead of only 1 buffer i have 3 buffers (buffer_1 ,buffer_2,buffer_3) and needs to pick some % of count from each buffer how can we add this in the same code. for example if you run the code 1st row remainign column value 43 so 43 required for that 43 i need three columns like buffer_1 22(50%43) and buffer_2 value(30%43) and (buffer_2 value(20%43)

 

Buffer 1000            
               
Buffer_1 500 50%   Remain Buffer_1 Buffer_2 Buffer_3
Buffer_2 300 30%   43 22 13 9
Buffer_3 200 20%          

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
  • 9 replies
  • 1367 views
  • 4 likes
  • 5 in conversation