topic Re: I am importing an excel sheet to sas . One of the excel cells contain unknown number of strings in SAS Programming

I am importing an excel sheet to sas . One of the excel cells contain unknown number of strings.

mathewse111 — Tue, 08 Jun 2021 18:37:05 GMT

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.

Re: I am importing an excel sheet to sas . One of the excel cells contain unknown number of strings

Sajid01 — Wed, 09 Jun 2021 01:00:42 GMT

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

Re: I am importing an excel sheet to sas . One of the excel cells contain unknown number of strings

ballardw — Wed, 09 Jun 2021 14:48:37 GMT

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.

Re: I am importing an excel sheet to sas . One of the excel cells contain unknown number of strings

Tom — Wed, 09 Jun 2021 15:54:16 GMT

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?