topic Re: Reg Loading Data for Delimited file in SAS Programming

Reg Loading Data for Delimited file

sas_Forum — Tue, 27 Sep 2011 12:44:54 GMT

HI i am having a problem while loading the delimited file

name^dec
fridge^fridege is mainly use for:

1.for keeping water
2.for storage

cooler^mainly used for

1.cool air
2.cool temperature

Note: The line is not ending proparly as there is <enter> in between the observation
how can i do,can any one help me as i am having many variables and huge data

output

name    desc
fridge    fridege is mainly use for:1.for keeping water 2.for storage
cooler   mainly used for 1.cool air 2.cool temperature

Reg Loading Data for Delimited file

art297 — Tue, 27 Sep 2011 13:00:37 GMT

You might not want to read it in as a delimited file but, rather, skip the header record, read in the three strings, use scan to separate the first string into name and dec, and then use one of the cat functions to combine dec with the other two strings.

Reg Loading Data for Delimited file

Peter_C — Tue, 27 Sep 2011 20:37:45 GMT

sometimes such data comes from excel which uses an unusual line-ending character LF for in-cell formatting, rather than the more normal (in windows) CRLF.

Supporting unix and windows data, SAS by default treats the LF as signalling the line-ending.

SAS9.2 introduced a new infile option to better support that excel-in-cell-formatting situation.

This infile option TERMSTR= CRLF might fix it for you (if this stand-alone LF is your problem)

there is also a usage example designed to solve the problem without using the TERMSTR= option.

See http://support.sas.com/kb/41/116.html entitled Sample 41116: Removing unwanted Line Feeds from delimited files.

The "FullCode" tab shows a short program you adapt to suit your data.

Re: Reg Loading Data for Delimited file

BobD — Tue, 27 Sep 2011 20:54:03 GMT

You really don't have a delimited file; it's just an awkward file to read. Assuming the file is exactly as you describe, this should do the trick:

data want;
   infile 'path-to-your-file' truncover firstobs=2 end=eof;
   length name $20 desc $200;
   retain name desc;
   keep name desc;

input @1 dataline $200.;

   if _n_ = 1 then do;
      name = scan(dataline,1,'^');
      desc = scan(dataline,2,'^');
      end;

   else
      if index(dataline,'^') then do;
         output; /* Output previous record */
         name = scan(dataline,1,'^');
         desc = scan(dataline,2,'^');
         end;
      else desc = catt(desc,dataline);
   put desc=;
   if eof then output; /* Output last record */
run;

The CATT function trims and concatenates the variables; if you want a "blank" inserted between the rows, use the CATX function.