Desktop productivity for business analysts and programmers

Text to columns into a SAS form

Reply
New Contributor
Posts: 4

Text to columns into a SAS form

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

Community Manager
Posts: 2,889

Re: Text to columns into a SAS form

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.

fw.png

Chris

New Contributor
Posts: 4

Re: Text to columns into a SAS form

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

Super User
Super User
Posts: 7,711

Re: Text to columns into a SAS form

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.

New Contributor
Posts: 4

Re: Text to columns into a SAS form

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

New Contributor
Posts: 4

Re: Text to columns into a SAS form

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

Contributor dkb
Contributor
Posts: 53

Re: Text to columns into a SAS form

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.

Contributor
Posts: 25

Re: Text to columns into a SAS form

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.

MyExample.jpg

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


Super User
Super User
Posts: 7,711

Re: Text to columns into a SAS form

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

Ask a Question
Discussion stats
  • 8 replies
  • 3417 views
  • 6 likes
  • 5 in conversation