Create a new column

[SanchitArora]

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.

[Steelers_In_DC]

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.

[ballardw]

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...

[SanchitArora]

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.

[Ksharp]

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;

[SanchitArora]

Hey thanks,

It Helped me .