BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kyle_fredette
Calcite | Level 5

I am currently building a solution in Excel that will allow users to enter information on a template and have SAS import the template for calculations. The template will include start and end dates for a given condition. In this case, the condition refers to the department that employees work within. Since employees change departments, we want to track data based on which department they completed the work for.

Currently, I am looking at this in two different ways (see charts below). Table one has the employees enter changes by including new rows of data. Table two has the employees enter changes by including new columns of data. Does anyone have experience with which of these data structures works best for longevity and flexibility? I want the best long term solution.

Thanks for your time!

Kyle

Vertical
EmployeeDepartmentStart dateEnd date
JohnFinance1/1/20153/31/2015
JohnMarketing3/31/201512/31/2015
MaryOperations1/1/201512/31/2015
SusanSales1/1/2015

12/31/2015

Horizontal
EmployeeDepartmentStart dateEnd dateTeamStart dateEnd date
JohnFinance1/1/20153/31/2015Marketing3/31/201512/31/2015
MaryOperations1/1/201512/31/2015
SusanSales1/1/201512/31/2015
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Vertical, it is a common data structure, and you can easily determine which is the latest/first via sorting by date. You can either leave the end date missing for current positions or set it to something like Dec 31 9999.

FYI This is called a Type 2 Slowly Changing Dimension (SCD)

Slowly changing dimension - Wikipedia, the free encyclopedia

View solution in original post

3 REPLIES 3
Steelers_In_DC
Barite | Level 11

I think vertical is the way to go, add a date field for when the record was added and changed and you can sort by that if necessary, horizontal can get ugly depending on how long this is in use.

Reeza
Super User

Vertical, it is a common data structure, and you can easily determine which is the latest/first via sorting by date. You can either leave the end date missing for current positions or set it to something like Dec 31 9999.

FYI This is called a Type 2 Slowly Changing Dimension (SCD)

Slowly changing dimension - Wikipedia, the free encyclopedia

AnnaBrown
Community Manager

Also, LinusH wrote an article series on SCDs that may be helpful.


Join us for SAS Community Trivia
SAS Bowl XXIX, The SAS Hackathon
Wednesday, March 8, 2023, at 10 AM ET | #SASBowl

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 3344 views
  • 7 likes
  • 4 in conversation