BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
hellind
Quartz | Level 8

 

May I know what is the below delimiter character? I did a Google search, apparently it is 'Control A' character?

Then my question is how do I open the delimited exported file in Excel? What delimiter should I put for Data Import Wizard?

 

DBMS = "DLM"
REPLACE;
DELIMITER ='01'X;

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

If you are using the Get Data wizard

Tom_1-1654624988388.png

it should figure it out on its own.

 

Tom_0-1654624948504.png

 

View solution in original post

5 REPLIES 5
Reeza
Super User

'01'x is the hex code delimiter. 

From the lookup table it seems to be Start of Header or ^A.

'09'x is a common one and means a tab.

 

https://www.techonthenet.com/ascii/chart.php

 


@hellind wrote:

 

May I know what is the below delimiter character? I did a Google search, apparently it is 'Control A' character?

Then my question is how do I open the delimited exported file in Excel? What delimiter should I put for Data Import Wizard?

 

DBMS = "DLM"
REPLACE;
DELIMITER ='01'X;

Thanks.


 

Tom
Super User Tom
Super User

If you are using the Get Data wizard

Tom_1-1654624988388.png

it should figure it out on its own.

 

Tom_0-1654624948504.png

 

hellind
Quartz | Level 8

 

Excel -> Get External Data --> Text Import Wizard was not able to auto-detect the delimiter.

 

But Get Data -> From File was able too.

 

Thanks. 

ballardw
Super User

If you want to create something that opens in Excel on EXPORT then first choice would be a COMMA delimiter. You don't show the file name or other options so can't go much farther than that.

 

Your question is pretty confused mixing "open in Excel" and "Data Import wizard". The export to Excel would imply that you already have a SAS data set and writing that data set out. If you are importing the data then you use whatever actual delimiter appears in the data file if there is one.

 

The '01'x is also a SOH character which with radio teletype meant "start of header" which was related to message traffic. Since the codes use for teletype are part of the basis for ASCII (one of the common character sets used in programming) then such characters are available.

 

Hint: Show entire procedure or data steps, not just part of the code.

 

hellind
Quartz | Level 8

 

The SAS script is a production code that exports the SAS dataset to text file using that '01'x delimiter which is then ingested by technology. 

IT department had requirement to use '01'x as delimiter, therefore I  can't change the type of delimiter to use. I prefer to use TAB as delimiter.

I wanted to do investigation in Excel and was trying to figure out what HEX code to center into the Excel -> Get External Data --> Text Import Wizard 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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
  • 5 replies
  • 1982 views
  • 2 likes
  • 4 in conversation