topic Re: Create a new column in SAS Enterprise Guide

Create a new column

SanchitArora — Thu, 30 Jul 2015 16:07:41 GMT

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.

Re: Create a new column

Steelers_In_DC — Thu, 30 Jul 2015 16:34:59 GMT

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.

Re: Create a new column

ballardw — Thu, 30 Jul 2015 16:38:55 GMT

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

Re: Create a new column

SanchitArora — Fri, 31 Jul 2015 13:58:06 GMT

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.

Re: Create a new column

Ksharp — Sat, 01 Aug 2015 12:32:06 GMT

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;

Re: Create a new column

SanchitArora — Fri, 28 Aug 2015 12:22:58 GMT

Hey thanks,

It Helped me .