DATA Step, Macro, Functions and more

datalines

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

datalines

Hi all.

I have two columns in Excel:

ab
1234reason
12345reason_flag
1234567reason_flag
12345678reason_flag
12345678910reason_flag

and I need to put them into SAS. It looks easier to put them under DATALINES neither IMPORT.

data result;

/*length a $20. b $20.;*/

input a$ b$ ;

datalines;

1234 reason

12345 reason_flag

1234567 reason_flag

12345678 reason_flag

12345678910 reason_flag

;

in case of SAS 9.x, all I have to do is remove /* */ from the second row and define maximum LENGTH.

The same approach gives TOTALLY DIFFERENT RESULT in SAS EG:

ab
1234 reason12345 reason_flag
1234567 reason_flag12345678 reason_fla

Could someone explain what the point is????

And what kind of approach might be used , if maximum length of column is unknown, not to loose any character?

Thank you in advance.


Accepted Solutions
Solution
‎11-23-2012 09:58 AM
Respected Advisor
Posts: 3,799

Re: datalines

Your problem is caused by TABs.  When you copy and paste from excel the fields are tab delimited.  How you deal with that is up to you.  You could used the INFILE statement option EXPANDTABS or read the file as table delimited DSD DLM='09'x, or something else.

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: datalines

All I know about EG is that it will wrap something around the code you wrote,

Whether or how it would affect the intended results, I can’t really comment on.  However, proc import aside, the following approach is considered more robust, given that 1.) Your maximum length is 20 2.) List input. Both methods will stop reading when 1) encountering delimiter, here is the default ‘blank’, 2) reaching maximum length you defined.

/* use colon*/

data result;

input (a b) (:$20.); ;

datalines;

1234 reason

12345 reason_flag

1234567 reason_flag

12345678 reason_flag

12345678910 reason_flag

;

/*use informat*/

data result;

informat a b $20.;

input a b; ;

datalines;

1234 reason

12345 reason_flag

1234567 reason_flag

12345678 reason_flag

12345678910 reason_flag

;

Haikuo

Occasional Contributor
Posts: 17

Re: datalines

Hai.kuo, your approach gives the same result as mine.

Please, try to copy data to the excel and from the excel back to the SAS.

PROC Star
Posts: 7,467

Re: datalines

To use datalines you have to do all of the copying and pasting, thus potentially introducing error.

I, personally, would save the file (from Excel) as a csv and then use the SAS proc import, setting the guessing rows value high enough that it encompasses all of your records.  Let it determine the necessary variable lengths.

Occasional Contributor
Posts: 17

Re: datalines

That's exactly what I finally did at the end, but, assume, there should be such kind of built in function to determine maximum length.

Solution
‎11-23-2012 09:58 AM
Respected Advisor
Posts: 3,799

Re: datalines

Your problem is caused by TABs.  When you copy and paste from excel the fields are tab delimited.  How you deal with that is up to you.  You could used the INFILE statement option EXPANDTABS or read the file as table delimited DSD DLM='09'x, or something else.

Occasional Contributor
Posts: 17

Re: datalines

Posted in reply to data_null__

thanks!

it works for SAS EG (not for SAS 9)

previously, i tried to define TAB delimiter. (commented)

data result;

/*infile datalines delimiter ='   ';*/

infile datalines DLM='09'x;

length a $19. b $19.;

input a$ b$ ;

datalines;

1234 reason

12345 reason_flag

1234567 reason_flag

12345678 reason_flag

12345678910 reason_flag

;

But is there somewhere else difference between SAS 9.x and SAS EG then?

PROC Star
Posts: 1,167

Re: datalines

This came up with me a few months ago. Enterprise Guide behaves differently from Display Manager; DM will by default consider TABS to be a delimiter in CARDS input, whereas EG won't. Here's a link to the Problem Note.

Personally, I like using the DATALINES with DLM='09'x solution; I've never had a problem with it.

Tom

http://support.sas.com/kb/17/257.html

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1173 views
  • 3 likes
  • 5 in conversation