BookmarkSubscribeRSS Feed
StefanG
SAS Employee

I have an Excel file that contains CRLFs into the cells.

In EG 4.1, when this file is imported, in the resulted dataset CRLFs are replaced with blanks.

However, in EG 5.1, when same file is imported, in the resulted dataset CRLFs are replaced with dots.

For example:

In Excel cell there is the following string:

(((

(ISP_GROUP1_SDESC IN ("A_Internet","A_Internet_FO","B_VPN","C_Frame Relay", , "B_VPN_Layer2")  AND PRICE_PLAN_TYPE_LDESC NOT IN ("Fixed Internet over Fixed Voice"))

OR

PRICE_PLAN_TYPE_LDESC IN ("Fixed Internet over Fixed Voice")

)

AND HFM_SEGMENT_SDESC = "TFSG110"

AND MASURA IN ("TD_ISP","CB_ISP","TA_ISP","OHP_CB_ISP_M1"))

OR

(COD_ACCOUNT="E8049000" and COD_2="TFSG110"  and COD_1="MVCB_ISP")

)

When the cell is imported in EG 5.1, the string becomes (partial output):

(((.(ISP_GROUP1_SDESC IN ("A_Internet","A_Internet_FO","B_VPN","C_Frame Relay", , "B_VPN_Layer2")  AND PRICE_PLAN_TYPE_LD

Notice the dot after the first three open parenthesis that replaces the CRLF in the original string.

As the intention is to use the initial Excel file to build a SQL phrase, the dot which appears leads to syntax errors.

Is there any way to circumvent the problem (other that eliminating CRLFs from original file)?

Thank you

Stefan

1 REPLY 1
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Sorry, I can't think of a workaround other than as you say, remove CLRF from the original file.  Maybe have a small VBA macro to do it on save?

I wouldn't recommend Excel for what you are trying to achieve either.  What you appear to be doing is using Excel to store some sort of metadata or part code and then read that in and create code from it.  Excel's strength and weakness is its flexibility.  If you are set on going forward with that kind of process I would suggest you need a lot more functionality to get it working.  Code checking, validation etc. which you could build into the Excel file in VBA.  Its one of the problems I am facing at the moment as we have base SAS and want to store partial code/metadata in SAS datasets, however the fsedit window is useless for data entry.  I believe that SAS do software for things like this, though there is a cost involved.  Alternatively you could develop a database yourself.  I saw at phuse a chap using RDF/OWL to store metadata for instance.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 1 reply
  • 1155 views
  • 3 likes
  • 2 in conversation