BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Denis
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

7 REPLIES 7
Haikuo
Onyx | Level 15

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

Denis
Calcite | Level 5

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.

art297
Opal | Level 21

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.

Denis
Calcite | Level 5

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.

data_null__
Jade | Level 19

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.

Denis
Calcite | Level 5

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?

TomKari
Onyx | Level 15

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

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!

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