BookmarkSubscribeRSS Feed
LinyuanQin
Calcite | Level 5

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?

13 REPLIES 13
LinyuanQin
Calcite | Level 5

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?

Patrick
Opal | Level 21

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;

Jagadishkatam
Amethyst | Level 16

@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
Patrick
Opal | Level 21

@

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.

Jagadishkatam
Amethyst | Level 16

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
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

which one is more sufficient ?

LinyuanQin
Calcite | Level 5

: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?

Vince28_Statcan
Quartz | Level 8

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.

Patrick
Opal | Level 21

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?

LinyuanQin
Calcite | Level 5

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!

Mit
Calcite | Level 5 Mit
Calcite | Level 5

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.

snoopy369
Barite | Level 11

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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 13 replies
  • 23716 views
  • 0 likes
  • 7 in conversation