Help using Base SAS procedures

How to widen a column length in IMPORT procedure?

Reply
Occasional Contributor
Posts: 12

How to widen a column length in IMPORT procedure?

Hi everyone:

I've written a piece of code like this:

proc import datafile="my directery"

out=test dbms=excel2000 replace;

...;

run;


In the dataset test the variable F1 had a length of 106, a format of $106. but it was still not wide enough to load a few of obs completely (they were in the last lines in excel and had a length of 140 or more). What I want is to widen the column, say, variable F1, to a length of 200, and have its format to be $200. . So how can i get it?

Respected Advisor
Posts: 4,173

Re: How to widen a colunm length in IMPORT procedure?

Posted in reply to LinyuanQin
Occasional Contributor
Posts: 12

Re: How to widen a colunm length in IMPORT procedure?

Thanks Patrick! But  I encounter another problem: if I use macro to import many excels at a time, the column F1 in the first to be imported excels has a length of 106, and in later ones, it has a length of 80, 120, 140, etc. In  this situation how to deal with such a problem?

Respected Advisor
Posts: 4,173

Re: How to widen a colunm length in IMPORT procedure?

Posted in reply to LinyuanQin

Using Proc Import I'm not aware of any way allowing you to pre-define a column attribute of the output table.

What you always can do is to create a mapping table and then re-create the output table, so something like:

data mapping;
  attrib 
    Name length=$140
    Sex length=$100
  ;

  stop;

  call missing(of _all_);

run;

data class;
  if 0 then set mapping;
  set sashelp.class;
run;

proc contents data=work.class;
run;

Trusted Advisor
Posts: 1,137

Re: How to widen a colunm length in IMPORT procedure?

@Patrick. the usage of below code will attribute the variables, however in the log we will get the note as variables uninitialized. 

data mapping;

  attrib 

    Name length=$140

    Sex length=$100

  ;

  stop;

run;

to avoid the same, we can try the below code

data class;

attrib 

    Name length=$140

    Sex length=$100

  ;

  set sashelp.class;

run;

proc contents data=work.class;

run;

Thanks,

Jagadish

Thanks,
Jag
Respected Advisor
Posts: 4,173

Re: How to widen a colunm length in IMPORT procedure?

Posted in reply to Jagadishkatam

@

It's only a Note - but I agree it's a Note to be avoided. Adding a "call missing()" will resolve this. I've amended the code accordingly in my last post.

As for your alternative solution: Creating a separate "mapping" data set has the advantage that you can use such a "template" in multiple data sets. Something the OP requires.

Trusted Advisor
Posts: 1,137

Re: How to widen a colunm length in IMPORT procedure?

Thanks Patrick, i agree with you.

Yes, if there are many datasets that need to updated especially with regard to the variable attributes, then this is a good solution. The only thing that we need to be careful when using this mapping dataset is that we need to use the drop option or statement to drop the variables that are not present in the dataset but are there in the mapping dataset.

i like your solution of using call missing(of _all_).

Regards,

Jagadish

Thanks,
Jag
Super Contributor
Super Contributor
Posts: 444

Re: How to widen a colunm length in IMPORT procedure?

Posted in reply to Jagadishkatam

is this mapping set thing similar  to proc datasets lib=mylib;modify dataset;length var1;length var2,......; ?

which one is more sufficient ?

Occasional Contributor
Posts: 12

Re: How to widen a colunm length in IMPORT procedure?

:Thanks again, Patrick! Your code does make a certain variable's format to be a specialised format, but it's made after the data set has been created. In my situation, after importing excels, the uncompleted data has been loaded in data set, and widen the length still could not achieve completion of it. So is there any other statement could deal with it?

Super Contributor
Posts: 339

Re: How to widen a colunm length in IMPORT procedure?

Posted in reply to LinyuanQin

As far as I know, import sets the length to that of the first value encountered for the given variable. There are different solutions depending on how you obtain your data source and what/where you can intervene in the process. For instance, you could insert an additionnal row at the top of your data with desired fixed length (would have to be done through excel - or you could consider DDE if desperate). You could also consider exporting your excel sheets as CSV and reading them through data steps instead of proc import w sas/access. In a similar train of thought you could consider saving excel sheets as xml instead of xls and importing them with the xml92 engine.

I am unaware of any length specification for proc import. Hence the alternative suggestions.

proc access doesn't seem to have a length alternative either. Using directly pc files engine with libname statement would probably have the same issue that excel sheets viewed through the engine have a truncated length.

Respected Advisor
Posts: 4,173

Re: How to widen a colunm length in IMPORT procedure?

Posted in reply to LinyuanQin

I'm not sure that I fully understand what the remaining problem is but basically: You need to re-create the data set after the import step if you want to change the length of a variable. In SAS the input and output data set can be the same, so:

data have;

  set mapping have;

run;

You can also change variable attributes using a SQL ALTER TABLE but for changing the length of variables the data set will also get re-created. So from a processing point of view these 2 approaches are the same.

You could also program for a more dynamic approach by first importing all the Excels into a common SAS library, then use sashelp.vtable to determine the max length for each variable, and then apply this max length to all of your data sets (this will require some macro coding) or use the information to dynamically create the mapping dataset.

Can you explain in detail why such an approach is not working for you?

Occasional Contributor
Posts: 12

Re: How to widen a colunm length in IMPORT procedure?

Thanks everyone! It seems that i haven't describe clearly in details... It's a little bit weird to read reported table in excel into sas dataset... Now i upload ten excel file as a sample (it remains in Chinese characters). The code i wrote is as below:

%let my_dir = e:\xls\ ;

filename xcl_fil pipe "dir &my_dir.*.xls /b";

%MACRO Mult_Fil(PIPEin=,DSout=);

%LOCAL i ;

    data _NULL_ ;

       infile &PIPEin end=last;

       retain k 0 ;

       k + 1 ;

       length f_name1 f_name2 $ 60 ;

       input f_name1 $ ;

       f_name2 = "&my_dir"||trim(left(f_name1));

       ck = compress(put(k,3.));

       call symput('G'||ck,f_name2);

       if last then call symput('TOT_FILZ',ck);

    run;

    data &dsout ;

    run;

%DO i = 1 %TO &TOT_FILZ;

proc import datafile="&&G&i"

out=test dbms=excel2000 replace;

range='a1:i50';

getnames=no;

run;

data test1;

set test(drop=f1);

if f3=. then delete;

run;

proc transpose data=test(keep=f1 where=(f1 ne ' '))

out=test2(drop=_name_ _label_ col1 );

var f1;

run;

data test3(drop=col6);

set test1;

if _n_=1 then set test2;

run;

    data &dsout ;

         set &dsout test3 ;

       run;

%END ;

    data &dsout ;

       set &dsout;

       if _N_ = 1 then delete;

    run;

%MEND Mult_Fil ;

%mult_fil(PIPEin=xcl_fil,DSout=perm);

After submitting this program, we could see that COL3 has a format of $106. and a length of 106, so some characters had not been read completely. The strangest thing about it is that if the first two excels (10预防本科卫生统计学(理论).xls, 10预防本科卫生统计学(实验).xls) were deleted from e:\xls, remaining eight excels in it, submitting this program could make COL3 a format of $150. and a length of 150. This makes me puzzled, could anyone explain it and edit my code to make COL3 a format of $200. disregarding how many excels in e:\xls?

I appreciate any help you provide!

Attachment
Frequent Contributor
Frequent Contributor
Posts: 83

Re: How to widen a column length in IMPORT procedure?

Posted in reply to LinyuanQin

Though it is not a permanent solution.  If the problem is for only one variable then try importing data with the highest variable length first.

Super Contributor
Posts: 253

Re: How to widen a column length in IMPORT procedure?

Posted in reply to LinyuanQin

You can control, to some extent, things with DBSASTYPE, like explained in this other post at the end:

https://communities.sas.com/thread/35587?start=15&tstart=0

It's finnicky, and requires SAS 9.2 I believe, but it does let you specify character lengths.

Ask a Question
Discussion stats
  • 13 replies
  • 4504 views
  • 0 likes
  • 7 in conversation