SAS Enterprise Guide

Desktop productivity for business analysts and programmers
BookmarkSubscribeRSS Feed
SanchitArora
Calcite | Level 5

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.

5 REPLIES 5
Steelers_In_DC
Barite | Level 11

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
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

Hey thanks,

It Helped me .

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1724 views
  • 4 likes
  • 4 in conversation