Hi, i have 2 columns i.e. material no., week, quantity.
Material no: week Quantity
10000000012119 _47_2015 10000
10000000012119 _48_2015 2000
10000000012119 _49_2015 3871
10000000012119 _50_2015 2568
10000000012119 _51_2015 2221
10000000012119 _52_2015 43132
10000000012140 _26_2015 2321
10000000012140 _27_2015 1234
10000000012140 _48_2015 2134
10000000012140 _50_2015 6728
10000000012140 _26_2016 25612
10000000012140 _27_2016 67229
Now i need to create a new column which will start from 1 for a small week and add 7 to the corresponding week. For eg: As for week 26 it will put 1 and for week 27 it will put 7 and for week 28 it will keep 14...have to keep in mind the year also as for 2016 corresponding value will increase. As i am working in EG 5.1 through GUI basis , so would appreciate if you can help me making the formula in advance expression in the commuted column in query builder.
You want to use a sum statement. count + 7 will get you the counter you want. You will have to reset the counter at the first material I assume. I'm not familiar with the query builder so this is as far as I can help.
Please show your intended output for the example data provided.
From the description I can come up with several different outputs that match the text which means at least one of them isn't what you want...
output required will be as:
Material no: week Quantity Output
10000000012119 _47_2015 10000 147
10000000012119 _48_2015 2000 154
10000000012119 _49_2015 3871 161
10000000012119 _50_2015 2568 168
10000000012119 _51_2015 2221 175
10000000012119 _52_2015 43132 182
10000000012140 _26_2015 2321 1
10000000012140 _27_2015 1234 7
10000000012140 _48_2015 2134 154
10000000012140 _50_2015 6728 168
10000000012140 _26_2016 25612 364
10000000012140 _27_2016 67229 371
So in the above output you can see that the min week has 1 , then 7 is added for the corresponding week. EG: week 26 have 7, week 27 have 14......week 27of 2016 has 371.
would appreciate if you could provide result in the advance expression of SAS EG5 5.1 as i don't use coding.
How about :
data have;
input Material : $40. week : $20. Quantity;
cards;
10000000012119 _47_2015 10000
10000000012119 _48_2015 2000
10000000012119 _49_2015 3871
10000000012119 _50_2015 2568
10000000012119 _51_2015 2221
10000000012119 _52_2015 43132
10000000012140 _26_2015 2321
10000000012140 _27_2015 1234
10000000012140 _48_2015 2134
10000000012140 _50_2015 6728
10000000012140 _26_2016 25612
10000000012140 _27_2016 67229
;
run;
data temp;
set have;
w=input(cats(scan(week,-1,'_'),'W',scan(week,1,'_')),weeku.);
format w yymmdd10.;
run;
proc sort data=temp;by Material w;run;
data want;
set temp;
by Material ;
retain f;
if first.Material then do;f=w;output=1;end;
else output=intck('week',f,w)*7;
drop f;
run;
Hey thanks,
It Helped me .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.