BookmarkSubscribeRSS Feed
mathewse111
Calcite | Level 5

My excel input contain 24 fields. One of the cells in the excel sheet is paths of file. and their will be multiple paths separated by linefeed.  The number of file paths in each cell  differs. In another cells holds multiple file names. The number of file names in each cell differs.   I did import the excel sheet to a sas dataset. The object of the program  is- whenever a new row is added to the excel sheet, if the added date is <=today()-1, then check for duplicates in the filename, if true check for duplicate file paths.

 

I  need help in resolving this oiissue.

3 REPLIES 3
Sajid01
Meteorite | Level 14

One of the fundamental aspects in analytics is data preparation.
You need to pre process your excel spreadsheet to make it suitable for SAS.
While SAS can be capable of doing many things, design your spreadsheets for SAS
Identify the common elements in that paths and separate them into columns.
This will save time and makes working easier

ballardw
Super User

I have a sneaking suspicion that whatever you are doing, if you get it to work, will fail in the future.

For one thing, you will have to "import" the file after every change.

Second "import" tools will guess as to the length of variables using a small number of rows. If you have longer values after that number of rows you have a very high likelihood of truncated (incomplete) data.

 

You will likely have a better chance of success if you make individual rows for each path/file entry and don't let anyone insert multiple ones into a single cell.

 

One reason for the individual rows is that one of the first things you would have to do for the "duplicate" check to be efficient is to create a data set with one observation per. Which with possible truncated data is going to have other issues.

Tom
Super User Tom
Super User

Please construct a simple example of what you are trying to do to clarify your problem. 

 

You probably do not need to include all 24 fields or more than two or three paths per example row.  Also for purpose of sharing your example it will be easier to use | or some other character instead of LineFeed to delimit the paths:

data have;
  infile cards dsd truncover ;
  input v1 :$10. v2 v3 paths :$200. ;
cards;
A,1,2,/one|/two
B,2,3,/three|/four
;

Then show what output you want from this example.

 

How is it that you know something new is added?  Is one of the fields a date or datetime value?

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 294 views
  • 0 likes
  • 4 in conversation