An Easy Way to Calculate Pallets

There are online Pallet calculators available for you to calculate # of pallets required for a certain quantity of each product you have for warehousing or shipping, but it is not easy to find the appropriate calculator/formula for you to use in Excel for calculations with massive # of rows.

One easy formula I came up to calculate # of pallets required with the following inputs (unit: inches):
* L for Length
* W for Width
* H for Height
* Q for Quantity

For simplicity, here we start with fixed length of 48 inches and fixed width of 48 inches as well as a fixed height of 48 inches for a pallet.

Step 1: calculate minimum # of pallets required for 1 unit of the product

Min Pallet per Unit = ROUNDUP(MAX(L, W, H)/48,0)

Step 2: calculate estimated of pallets required for Q units of the product

estimated # of Pallets = (Min # of Pallets per Unit) * ROUNDUP(Q*L*W*H/((Min # of Pallets per Unit)*48^3),0)

Examples:
SKU Length Width Height Quantity Min # of Pallets per Unit # of Pallets
12345 28 2 3 100 1 1
12346 49 2 3 100 2 2
12347 28 10 5 500 1 7

SSMS – How to Start SQL?

SSMS stands for SQL Server Management Studio, which is a Microsoft product. SQL stands for Structured Query Language. Here we will summarize the basics on how to start using SSMS or write SQL queries.

1. have SSMS installed
2. have access and connect to a SQL server
3. get to know the database(s) and tables in each database on the server
4. learn basics on SQL query (http://www.tutorialspoint.com/sql/; https://www.w3schools.com/sql/)
5. practice

Below are some basics:

• click “new query” to start a new SQL query
• “- -” or “/* */” for comments
• basic syntax:  select x1,x2,…,y1,y2 into output_table from database_name.schema_name.table_name
• basic functions
  • aggregated functions: count(), sum(), min(), max(), avg()… with “group by“
  • string functions:
  • date time functions: getdate(), dateadd(), convert(), datediff(), datepart(); Below are examples of

    DATEPART(YEAR(or MONTH or DAY or HOUR or MINUTE), [date])
    cast(convert(varchar(8), dateadd(dd, -9, getdate()), 112) as int)  /*ISO*/

  • NULL functions: isnull()
• Operators
  • Arithmetic operators: +, -, *, /, %(modulo)
  • Bitwise operators: &, |, ^
  • Comparison operators: =, >, >=, <, <=, <>
  • Logical operators: AND, OR, NOT, IN, LIKE, BETWEEN, EXISTS, ANY, SOME, ALL
    • email_address not like ‘%noemail%’
• Data types
  • char(), varchar(n), varchar(max), text, nchar, nvarchar, nvarchar(max), ntext
  • bit, binary(n), varbinary(), varbinary(max), image
  • numeric(p,s), decimal(p,s), float(n), real, money, int, bigint, smallint, tinyint
  • datetime, datetime2, date, time
• SQL statement (https://www.w3schools.com/sql/sql_quickref.asp)
• Macro variable
  • DECLARE @start_date_iso INT = (SELECT MIN(date_iso) FROM dim_date dt WITH(NOLOCK) WHERE dt.year = 2017 AND dt.period = 1)
• temporary/permanent output table
  • select * from input_table_name into #output_temp_table_name
  • if object_id(‘tempdb..#member’) is not null
    drop table #member

Useful tools/links for online analysis

Is your browser safe against tracking?
https://panopticlick.eff.org/

Adobe Debugger:
https://marketing.adobe.com/resources/help/en_US/sc/implement/debugger_install.html

Adobe “Ad Hoc Analysis” to run quick analysis and pull data: (tools-> ad hoc analysis)
https://www.adobe.com/training/video.html#more-videos

Cool Chrome Extensions:
* SimilarWeb – Site Traffic Sources and Ranking
* SimilarTech – Discover prospecting information on any website you’re visiting by simply clicking the extension’s icon

Track website traffic:

• SimilarWeb :  http://www.similarweb.com
• SitePrice:  http://www.siteprice.org
• StatChest: http://www.statchest.com
• TrafficEstimate: http://www.trafficestimate.com/
• SemRush: https://www.semrush.com/
• TraffikLite:  https://www.trafiklite.com/
• Quantcast
• Google Analytics

Track website speed:

https://uptimerobot.com/

Google Analytics/Adobe Analytics etc.

 

 

Create Dynamic Subsets from a Dataset

data sample;
input group dates date9.;
datalines;
1 01Jan2016
1 10Feb2016
2 02Feb2016
2 16Mar2016
2 19Mar2016
5 20Jan2016
;
run;
proc freq data=sample(keep=group) noprint;
table group/list missing out=group(keep=group count);
run;
%macro subset;
data g&group_id.;
set sample;
if group=&group_id.+0;
format dates date9.;
run;
%mend;
data _null_;
set group(rename=(group=group_id)) end = eof;
call symput(‘group_id’,compress(group_id));
call execute(‘%subset’);
run;

Google trends

To download Google search data, you may go to Google trends:

https://www.google.com/trends/

You may select a specific country or worldwide, have choices of time span, the category etc.

You may download the weekly .CSV data if you are signed in with your Gmail account.

SAS Dates Transform and Calculation

Dates formats will be covered in this post:

• iso: International Organization for Standardization (ISO) date format is a standard way to express a numeric calendar date that eliminates ambiguity. F0r example, today is “April 11, 2016”, it is “20160411” in date ISO format.
• date9.: in SAS date9. format, today is “11APR2016”.
• datetime: for now, it is 11APR2016:04:56:07.103

*iso date to sas date;
%macro mdy(date_iso, date);
year = int(&date_iso./10000);
day = mod(&date_iso.,100);
month = int((&date_iso.-year*10000)/100);
&date.=mdy(month,day,year);
drop year month day;
%mend;

*days between two iso dates;
%macro days_btw(date_isof, date_isot, days_btw);
%mdy(&date_isof., dfrom);
%mdy(&date_isot., dto);
&days_btw. = dto – dfrom + 1;
drop dfrom dto;
%mend;

For example, let “test” be a data set with one column and one row “create_datetime”=04JUN2013:09:10:57.487, the following DATA step will get the dates in date9. format (create_date, create_date2) and in ISO format (create_date_iso).

data test1;
set test;
format create_date create_date2 date9.;
create_date = datepart(create_datetime);

create_date_iso=year(create_date)*10000+month(create_date)*100+day(create_date);
/*create_date_iso=compress(year(create_date)
||put(month(create_date),z2.)
||put(day(create_date),z2.))+0;  *another way; */
%mdy(create_date_iso,create_date2);
run;

Color coding a column based on another column

While working on Excel, we may need to color code a column “Region” based on the values from a different column “Value” with three colors as above:

1. the region is colored “Red” if the value is <40
2. the region is colored “Yellow” if the value is between 40 and 65 (both exclusive)
3. the region is colored “Green” if the value is between 65 and 100 (both inclusive)

Set these three rules for cell A2-A15 one after another by repeating the following steps for each “New Rule”:

• Click Conditional Formatting from the top ribbon
• Select New Rule
• Click Use a formula to determine which cells to format, then put the formula and set the format

 

SAS Macro to Read through a Variable List

It is quite often to list different types of variables as strings/variable lists for modeling. For example, you fit your model and get:

y_hat = intercept + parest1*var1 + parest2*var2 + … + parestk*vark

where k could be as large as a couple of hundred. It will be really tedious to write each of the k independent (note that the number k could be changing from model to model as well as the names of the independent variables) to find the predicted value. For example:

%let indpt_var = var1 var2 … vark; *list of all independent variable names;
%macro varl(list,id);
%global var_&id.;
%let i = 1;
%let var_&id.=&id..intercept; *if including the intercept term;
%do %while (%cmpres(%scan(&list., &i.)) ne );
%let item&i. = %cmpres(%scan(&list., &i.));
%let var_&id.=%cmpres(&&var_&id.+&id..&&item&i.*x.&&item&i.);
%let i = %eval((&i. + 1);
%end;
%mend;
%varl(&indpt_var.,m1);

*find the predicted y values at each x value
proc sql;
create table pred as
select x.*, &var_m1. as y_hat
from x_pred as x
left join est as m1 on 1=1;
quit;

One simple example of using TraMineR package

Below is a simple example to find and plot the most frequent sequences/sub-sequences by using TraMineR library:

#install TraMineR package;
install.packages(“foreign”)  #to access the functions provided by the package.
install.packages(“TraMineR”)
library(TraMineR)

data=read.csv(“C:/Documents/R/Cat_Seq.csv”)
data.seqe = seqecreate(id=factor(data$id), timestamp=data$date, event=data$event)
fsubseq = seqefsub(data, minSupport=1000)
plot(fsubseq[1:15], col=”cyan”)

Notes: 1. need to first install “foreign” package before “TraMineR”; 2. if your “id” column is long, better use factor(); 3. “event” column can’t contain characters such as “,”

 

Reboot Remote Computer (on VPN)

use command prompt line:

shutdown -r -f

To get command prompt, search for and click “Command Prompt”.