Prev: %do ind=a %to z; in Macro
Next: Filling up missing data
From: jarrad.taylor on 12 Aug 2010 03:35
Hi All,

Hoping someone can shed some light on an issue I am encountering at
work.

I am attempting to import an Excel sheet into SAS (enterprise guide
4.1 on Windows 2000 server, using a code node) using proc import. Once
the sheet has been converted into a SAS dataset I add a couple of
columns and then export out to a piped ("|") delimited file, with no
header.

My problem is that some of the data contained in the excel sheet
contains leading blanks, which needs to be preserved throughout this
process, that is to say that the piped delimited file should also
contain the leading blanks within the field.

The proc import code is:

proc import datafile = 'C:\SAS Exports\Test Mapping File.xls'
out = Mapping_File
dbms = excel REPLACE;
sheet = "Sheet 1";
run;

The proc export code is:

data _null_;
file 'C:\SAS Exports\Mapping File For I Series.txt' dsd dlm = '|';
set mapping_file2;
put (_all_) (+0);
run;

SAS seems to format the field when importing it, subsequently
stripping the leading blank. I want to be able to use an informat of
$CharW. so that the leading space is preserved, but can't figure out
how to do this.

As an example the input data is (first row in the Excel sheet)
BOOROWA NSW 2583 BOOROWA 1/07/2010 JT12H

And the first row of the output file is (note that " BOOROWA" is now
"BOOROWA")
BOOROWA|NSW|2583|BOOROWA|01/07/10|JT12H|12/08/10|10:27:41

Any help would be greatly appreciated.
From: data _null_; on 12 Aug 2010 08:36
On Aug 12, 2:35 am, "jarrad.taylor"
<jarrad.tay...(a)eldersinsurance.com.au> wrote:
> Hi All,
>
> Hoping someone can shed some light on an issue I am encountering at
> work.
>
> I am attempting to import an Excel sheet into SAS (enterprise guide
> 4.1 on Windows 2000 server, using a code node) using proc import. Once
> the sheet has been converted into a SAS dataset I add a couple of
> columns and then export out to a piped ("|") delimited file, with no
> header.
>
> My problem is that some of the data contained in the excel sheet
> contains leading blanks, which needs to be preserved throughout this
> process, that is to say that the piped delimited file should also
> contain the leading blanks within the field.
>
> The proc import code is:
>
> proc import datafile = 'C:\SAS Exports\Test Mapping File.xls'
> out = Mapping_File
> dbms = excel REPLACE;
> sheet = "Sheet 1";
> run;
>
> The proc export code is:
>
> data _null_;
> file 'C:\SAS Exports\Mapping File For I Series.txt' dsd dlm = '|';
> set mapping_file2;
> put (_all_) (+0);
> run;
>
> SAS seems to format the field when importing it, subsequently
> stripping the leading blank. I want to be able to use an informat of
> $CharW. so that the leading space is preserved, but can't figure out
> how to do this.
>
> As an example the input data is (first row in the Excel sheet)
>  BOOROWA        NSW     2583    BOOROWA 1/07/2010       JT12H
>
> And the first row of the output file is (note that " BOOROWA" is now
> "BOOROWA")
> BOOROWA|NSW|2583|BOOROWA|01/07/10|JT12H|12/08/10|10:27:41
>
> Any help would be greatly appreciated.

I'm not clear where the leadin blanks get lost, IMPORT or EXPORT?.
 | 
Pages: 1
Prev: %do ind=a %to z; in Macro
Next: Filling up missing data