BookmarkSubscribeRSS Feed
siope1kd
Calcite | Level 5

Hello All,

 

I have some code that I update each month for work that calculates a number of statistics over the last 12 months of shippment data for a number of different shipment groups i.e. plane, train, truck, ship etc.

 

In short, I have a column named shipment date which spans around 100 thousand rows and repeats the values 1-12 specifiying the month and another column named shipment count which gives the number of shipments on that date which looks something like this:

 

data shipments
   input Shipment_Month Shipment Count;
   datalines;
1 2
2 3
3 5
4 6
5 7
6 9
7 10
8 11
9 12
10 11
11 8
12 7
1 .
2 .
3 .
4 .
. .
. .
. .
. .
. .

Each time I run the code there are two macro variables that update which give me the end of last month and the beggining of the month 12 months ago (spans 12 months). What i'd like to do is just create a column called Shipment_reindex which is populated by reindexed values 1-12 from the Shipment_Month column with the value of the macro that gives me 12 months back i.e. month(&Back_12_Months) = 5 rei-indexed to 1 and so on i.e. 5=1, 6=2, 7=3, 8=4, 9=5, 10=6, 11=7, 12=8, 1=9, 2=10, 3=11, 4=12.

 

Shipment_Month Shipment_Count Shipment_reindex
    1              2                  9
2 3 10
3 5 11
4 6 12
5 7 1
6 9 2
7 10 3
8 11 4
9 12 5
10 11 6
11 8 7
12 7 8
1 . 9
2 . 10
3 . 11
4 . 12
5 . 1
. .

 

I can think of the hard way to do it:

if Shipment_Month=5 then shipment_Reindex=1;

else if Shipment=6 then Shipment_Reindex=2

....

But i'd like for it to update the column automatically when I run the code instead of me constantly having to change the values. I've been messing around with arrays, but am getting nowhere fast. 

 

Please help me out!

 

Thanks,

- Keith

4 REPLIES 4
art297
Opal | Level 21

Couldn't you just include some code (or a macroized version of the following code) like:

 

data want (keep=shipment_date Shipment_Reindex);
  input shipment_date date9.;
  if month(shipment_date) lt month(today()) then
    month1=mdy(month(shipment_date),1,year(today())+1);
    else month1=mdy(month(shipment_date),1,year(today()));
  month2=today();
  Shipment_Reindex=intck('month',month2,month1)+1;
  cards;
12may2015
10dec2016
15mar2017
;

Art, CEO, AnalystFinder.com

 

robert215
Calcite | Level 5

HI,

 

Is lag and lead something that can help you ?

DATA shipments2;
SET shipments;
IF eof1=0 THEN DO;
SET shipments(firstobs=4
KEEP=Shipment_Month Shipment_Count
RENAME=(Shipment_Month=lead_Shipment_Month Shipment_Count=lead_Shipment_Count))
end=eof1;
END;
ELSE DO;
lead_Shipment_Month=.;
lead_Shipment_Count=.;
END;

lag_Shipment_Month=lag4(Shipment_Month);
lag_Shipment_Count=lag4(Shipment_Count);
RUN;

 

Patrick
Opal | Level 21

Hi @siope1kd

 

If I understand your process and requirement correctly then below should do the job.

/* data after appending latest month */
data have;
  format shipment_date date9.;
  do shipment_date="01Apr2015"d to "31Mar2017"d;
    Shipment_Month=month(shipment_date);
    Shipment_reindex=month(shipment_date);
    output;
  end;
run;

/* determine latest shipment_date in table */
proc sql noprint;
  select max(shipment_date) into :max_shipment_date
  from have
  ;
quit;

/* update Shipment_reindex using latest shipment_date in table */
proc sql;
  update have
    set 
      Shipment_reindex= 12-mod(intck('month',shipment_date,&max_shipment_date),12)
  ;
quit;
Patrick
Opal | Level 21

@siope1kd

 

The following is a bit more involved but it would allow you to apply the new "index" on-the-fly without a need to update your table.

 

The code uses a format on column "shipment_reindex". The specialty is that the format itself encapsulates the function which does the actual calculation. The only thing you would need to do on a monthly basis is to update the function instead of all the values in your have table. 

Actually: You wouldn't even need an extra column "shipment_reindex" but you just apply the format to column "shipment_date" whenever you need it.

 

Sample code below:

 

/* data after appending latest month */
data have;
  format shipment_date date9.;
  do shipment_date="01Apr2015"d to "31Mar2017"d;
    Shipment_Month=month(shipment_date);
    Shipment_reindex=shipment_date;
    output;
  end;
run;

/* determine latest shipment_date in table */
proc sql noprint;
  select max(shipment_date) into :max_shipment_date
  from have
  ;
quit;

/* recreate function using &max_shipment_date */
proc fcmp outlib=sasuser.funcs.myfuncs;
  function shipment_reindex(in_date) ;
    month_index = 12-mod(intck('month',in_date,&max_shipment_date),12);
    return (month_index);
  endsub;
run;

options cmplib=(sasuser.funcs);

proc format lib=sasuser;
  value _shipment_reindex
    other=[shipment_reindex()];
run;

/* apply format to dataset have */
proc datasets lib=work;
  modify have;
    format Shipment_reindex _shipment_reindex2.
  ;
  run;
quit;
  

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 881 views
  • 0 likes
  • 4 in conversation