|
View previous topic :: View next topic
|
| Author |
Message |
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Hello,
There is a SAS program,
| Code: |
DATA WELLS;
INFILE CARDS DLM=', ';
INPUT (VAR1-VAR4) ($);
CARDS;
A1,
B2,C3,
D4
E1,F2,
G3,H4
A1,B2,C3,
D4
;
RUN; |
This program quietly reads data and produces a nice output like below,
| Code: |
VAR1 VAR2 VAR3 VAR4
A1 B2 C3 D4
E1 F2 G3 H4
A1 B2 C3 D4 |
But if the data has embedded spaces in them then comes the problem.
Could someone please let me know how to read the below data into 4 variables.
| Code: |
A 1,
B 2,C3,
D4
E 1,F2,
G3,H 4
A1,B 2,C3,
D 4 |
Expected output:
| Code: |
VAR1 VAR2 VAR3 VAR4
A 1 B 2 C3 D4
E 1 F2 G3 H 4
A1 B 2 C3 D 4 |
I could try @@ but that maybe overkill.
Thanks in advance, |
|
| Back to top |
|
 |
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
| Have you tried INFILE CARDS DLM=', ' DSD; |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Hi Expat,
Tried DSD but did'nt work. Some values are not read into variables correctly. |
|
| Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
Well really Vasanthz, you have not posted the actual output to compare against the expected output, now go stand in the naughty corner  |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| EXPAT wrote: |
| you have not posted the actual output to compare against the expected output, |
you mean to allow for intuitive analysis of what is going on?
EXPAT, you expect to much.
Vsanathz, you are a much better technician than your post implies. |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
| Vasanthz, the & (ampersand) format modifier is a possibility. However, as this page indicates the requirement is for variables to be separated by at least two spaces: Reading embedded spaces with SAS |
|
| Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
| dbzTHEdinosauer wrote: |
Vsanathz, you are a much better technician than your post implies. |
Good spot Dick, my comments made were somewhat tongue in cheek |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Hi,
Thanks all in trying to help :-)
My bad. I forgot to copy paste the output which I got when I ran the program with 2nd set of data(possible culprit - hangover or lucid dreaming).
I will post it tomorrow when im at work.
Robert, & is a nice idea, I will try it out to see if I am able to get it working.
Regards, |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Hello,
I powered my PC SAS and tested Robert's suggestion. But SAS seems to ignore the comma as delimited when I tried "&".
Here are some of the things I tried out,
| Code: |
Expected output:
VAR1 VAR2 VAR3 VAR4
A 1 B 2 C3 D4
E 1 F2 G3 H 4
A1 B 2 C3 D 4
For the statement,
INFILE CARDS DLM=', ';
output was,
Obs VAR1 VAR2 VAR3 VAR4
1 A 1 B 2
2 D4 E 1 F2
3 G3 H 4 A1
For
INFILE CARDS DLM=', ' DSD;
output:
Obs VAR1 VAR2 VAR3 VAR4
1 A 1
2 B 2 C3
3 D4
4 E 1 F2
5 G3 H 4
6 A1 B 2 C3
7 D 4
For
INFILE CARDS DLM=',' ;
INPUT (VAR1-VAR4) (& $) ;
Output:
Obs VAR1 VAR2 VAR3 VAR4
1 A 1 B 2,C3
2 D4 E 1,F2 G3,H 4
For
INFILE CARDS DLM=', ' ;
INPUT (VAR1-VAR4) (& $) ;
Output:
Obs VAR1 VAR2 VAR3 VAR4
1 A 1 B 2,C3 D4 E 1,F2
|
| Quote: |
| Vsanathz, you are a much better technician than your post implies. |
Thanks for the compliment/pun (: |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
was not a pun, it is a statement of fact.
you know that one can not even start to 'debug' until
one knows what is happening. |
|
| Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
Try this :
| Code: |
INFILE CARDS DLM=',';
INPUT (VAR1-VAR4) ($8.);
|
|
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Hello Peter, I tried out your suggestion.
| Code: |
INFILE CARDS DLM=',';
INPUT (VAR1-VAR4) (:$8.); |
here is the output,
| Code: |
VAR1 VAR2 VAR3 VAR4
A1 B2 C3
D4 E1 F2
G3 H4 A1 B2 |
The problem I think is, for the record
A 1,
SAS reads A1 into VAR1 and when it encounters "," it reads the spaces into VAR2.
I could supress this behavior by mentioning spaces in DLM like
DLM='<space>,'. But mentioning space in DLM option causes incorrect reading of data with embedded spaces in them.
To test if this is the case,
I ran the below program with FB files and VB files.
INFILE 'WELLS.EXTERNAL.FILE.' DLM=',';
INPUT (VAR1-VAR4) ($);
For VB external file the output was same as expected output,
| Code: |
VAR1 VAR2 VAR3 VAR4
A 1 B 2 C3 D4
E 1 F2 G3 H 4
A1 B 2 C3 D 4 |
For FB external file the output was not correct due to trailing blanks.
| Code: |
VAR1 VAR2 VAR3 VAR4
A 1 B 2 C3
D4 E 1 F2
G3 H 4 A1 B 2 |
So I guess I will copy the whole file into a VB file and then process it.
If there are any other alternative please let me know.
Thank you, |
|
| Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
| (:$8.); was not in my sample. |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Peter, Thank you in trying to help.
I assumed you meant (:$8.) instead of ($8.) since this was list input.
Because if we read a delimited file using list input without a format modifier ($8.) then input statement will ignore the delimiter specified in DLM option and read 8 bytes of data even if it encounters a delimiter.
without format modifier:
| Code: |
DATA wells;
INFILE CARDS DLM=',';
INPUT VAR1 $8. VAR2 $8.;
CARDS;
1,3
AB,4
;
RUN; |
output:
| Code: |
Obs VAR1 VAR2
1 1,3
2 AB,4 |
With format modifier:
| Code: |
DATA wells;
INFILE CARDS DLM=',';
INPUT VAR1 :$8. VAR2 :$8.;
CARDS;
1,3
AB,4
;
RUN; |
Output:
| Code: |
Obs VAR1 VAR2
1 1 3
2 AB 4 |
|
|
| Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
| Yes, you are totally right. |
|
| Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
Haven't got access to try this but maybe you could 
It's a variation of code that I have used for processing a .csv file, and all I have done is remove the TRUNCOVER from the infile as you are not using a formatted .csv per se as your input, so without either of the MISSOVER or TRUNCOVER options specified should fill the obs as you want it.
| Code: |
INFILE FILE1 DLM="," ;
INPUT N1:$8.
N2:$8.
N3:$8.
N4:$8. ;
RUN; |
|
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Hello Expat,
Still no joy with the code, here is the output of the above code,
| Code: |
N1 N2 N3 N4
A 1 B 2 C3
D4 E 1 F2
G3 H 4 A1 B 2 |
Input:
| Code: |
A 1,
B 2,C3,
D4
E 1,F2,
G3,H 4
A1,B 2,C3,
D 4 |
Regards, |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Maybe it is a "philosophical" thing. Where you have a trailing comma, the implication (being made by SAS) is that you have another field containing a blank.
In fact, if you did have a final field that could contain a blank, that is how you'd want SAS to behave.
Whether the behaviour can be modified, I have no idea. |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Yes Bill, It seems to be the way SAS was designed. As a temporary workaround, I have copied the data to VB file and processed it until alternative way is found.
| Quote: |
| Whether the behaviour can be modified, I have no idea. |
Me too |
|
| Back to top |
|
|
|
|
