Hi all.
I have two columns in Excel:
a | b |
1234 | reason |
12345 | reason_flag |
1234567 | reason_flag |
12345678 | reason_flag |
12345678910 | reason_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:
a | b |
1234 reason | 12345 reason_flag |
1234567 reason_flag | 12345678 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.
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.
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
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.
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.
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.
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.
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?
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.