BookmarkSubscribeRSS Feed
harry_craggs
Calcite | Level 5

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

8 REPLIES 8
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
harry_craggs
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

harry_craggs
Calcite | Level 5

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

harry_craggs
Calcite | Level 5

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

dkb
Quartz | Level 8 dkb
Quartz | Level 8

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.

DaveBirch
Obsidian | Level 7

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


RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 8 replies
  • 7257 views
  • 6 likes
  • 5 in conversation