topic Re: Text to columns into a SAS form in SAS Enterprise Guide

Text to columns into a SAS form

harry_craggs — Tue, 21 Oct 2014 13:29:12 GMT

I have a large data set, in DAT format, that is sorted into rows whether it is imported into either excel or SAS.

In excel I can very easily transfer all this information into the layout I need by using Text to Columns. This allows the data to be sorted and also for me to be able to add columns wherever I feel.

However when it comes to SAS, both in my limited experience as a beginner and what I can find in forums, I don't believe there is anything that will do this the same way. I appreciate that the short term code writing will be slower but for future programming I hope to do this with one click.

If anyone has any ideas on the process or code that I would need to input to do this, please let me know.

Thanks

Harry

Re: Text to columns into a SAS form

ChrisHemedinger — Tue, 21 Oct 2014 13:36:29 GMT

If you're using SAS Enterprise Guide, look at File->Import Data. If your DAT file is fixed-width or delimited in some way, the Import Data wizard will detect the format and allow you complete control over how the data are imported. This screen shot is an example of the fixed-width control.

Chris

Re: Text to columns into a SAS form

harry_craggs — Tue, 21 Oct 2014 14:53:03 GMT

Thanks Chris,

Unfortunately I am using SAS 9.2 and have just realised that I have put this question in the wrong place.

If you have any ideas on that program I would be grateful but I will be reasking the question in the right group.

Apologies

Re: Text to columns into a SAS form

RW9 — Tue, 21 Oct 2014 15:03:29 GMT

Hi,

Whats the delimiter?

Should be as easy as:

data want (drop=I);

set have;

array var{100} $200.;

I=1;

do until (scan(text_string,i," ")=""); /* assumes space delimiter */

var{I}=scan(text_string,i," ");

I=I+1;

end;

run;

You could do it several ways really.

Re: Text to columns into a SAS form

harry_craggs — Tue, 21 Oct 2014 15:33:37 GMT

Here is an example of what I am trying to accomplish;

First of all the data has a few rows of text that I need to get rid of so the data currently starts at row 19 and only fills the 1st column.

rows variable1

1-18 (deletable text)

19 SSANGYONG MUSSO DIESEL 2874 1995-1999 ---5E D M 00100101 12D N2 23

20 SSANGYONG MUSSO SE DIESEL 1 2874 1995-1999 ---5E D M 00100201 13D N2 25

And the data carries on in that format.

I need to be able to split the data into columns of 15 variables. ie

variable 1 variable 2 variable 3 variable 4 var5 var6 var7 var8 var9 var10 var11 var12 var13 var14 var15

SSANGYONG MUSSO DIESEL 1 2874 1995 1999 5 E D M 00100201 13 D N2 25

This is straightforward in both Excel and in the example Chris showed however i cant get the data to separate using code.

I appreciate that the excess -- that are in the data can be trimmed afterwards so thats not a concern.

Thanks

Re: Text to columns into a SAS form

harry_craggs — Tue, 21 Oct 2014 16:07:33 GMT

Having got the process to work, using that code supplied above (thanks RW9), unfortunately the data does not have a common delimiter. So in some rows the data splits into more columns than in others. (MASSIVELY HELPFUL!!!!)

I don't suppose there is anyway of dividing it up considering there isnt a commonly placed delimiter??

Big ask

Re: Text to columns into a SAS form

dkb — Wed, 22 Oct 2014 04:00:43 GMT

SCAN can work with multiple delimiters, so you could for example amend RW9's code as follows:

do until (scan(text_string,i," ,;/")=""); /* multiple delimiters */

var{I}=scan(text_string,i," ,;/");

I=I+1;

end;

and it would then split at every blank, coma, semicolon or forward slash.

Re: Text to columns into a SAS form

DaveBirch — Wed, 22 Oct 2014 04:57:24 GMT

Hi Harry,

.DAT is commonly used as the file extension for fixed width text files. Unfortunately, you don't have access to the wizard-driven SAS EG. However, as you've said that in Excel you can easily get what you need by using Text to Columns, you use that to guide you in developing code similar to what SAS EG would do.

Using this information, I developed the following program:

data want;

infile 'mydir\example.dat' firstobs=19;

/* note Fixed Width handled with column-style input */

input var1 $ 1-14

var2 $ 15-44

var3 45-55

var4 56-60

var5 61-65 /* note don't require the dash */

var6 67-70

/* etcetra */

;

/* other statements as desired */

run;

Cheers

Re: Text to columns into a SAS form

RW9 — Wed, 22 Oct 2014 07:59:50 GMT

Good call, and you could also just save the file from Excel as CSV after processing the data.