- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
How about :
Code: Program
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hey thanks,
It Helped me .