How to add extra length (+1) to all variables

Reply
Occasional Contributor
Posts: 8

How to add extra length (+1) to all variables

[ Edited ]

I'm importing a text file using proc import, which assigns the variable lengths based on lrecl=32767.

 

My question is how can I make every variable 1 spot longer than the length SAS automatically assigned. I want to do this so I can then check to make sure no values were truncated by checking that the maximum value is at least 1 short of the variable length. There are over 100 variables so I do not want to do this manually if possible.

 

For example if I have 3 variables 

format AAA $20. ;
format BBB $5. ;
format CCC $10. ;

 

I want to make them like this, without having to type each one out:

format AAA $21. ;
format BBB $6. ;
format CCC $11. ;

 

 

Thanks in advance!

Occasional Contributor
Posts: 8

Re: How to add extra length (+1) to all variables

[ Edited ]

Are there any SAS wizards that can make a macro that would add +1 to current lengths for each variable I listed in the macro? Even if I had to format each variable individually, the more automated it can be the better.

 

Trusted Advisor
Posts: 1,131

Re: How to add extra length (+1) to all variables

I believe you need to import the file into a dataset and then you could try to increase the length and the way to do is explained with sashelp.class dataset.

 

first increase the length of the character variables with +1 and then create a macro variable len. Then apply this macro variable while creating the new dataset.

 

proc sql;
select case when xtype='char' then strip(name)||' $'||strip(put(length+1,best.))||'.' else '' end into: len from dictionary.columns where libname='SASHELP' and memname='CLASS';
quit;

data new;
length &len ;
set sashelp.class;
run;

Hope this helps.

Thanks,
Jag
Occasional Contributor
Posts: 8

Re: How to add extra length (+1) to all variables

Thanks for the response Jagadishkatam!

Is it possible to specify length in proc import? Otherwise I have to import using an infile data statement. The macro would have to work before the import took place otherwise there could still be truncated data that wasn't imported.
Trusted Advisor
Posts: 1,131

Re: How to add extra length (+1) to all variables

is guessingrows=32767 statement used in proc import
Thanks,
Jag
Occasional Contributor
Posts: 8

Re: How to add extra length (+1) to all variables

Yes but there are over 500,000 rows so its possible that may still truncate.

 

Is there a way to specify all string lengths to be 200 or something in proc import?

Trusted Advisor
Posts: 1,131

Re: How to add extra length (+1) to all variables

no by proc import we cannot specify the length. we need to specify the length in the datastep by input statement. So for the same we again need to identify the character variables and once done increase their length by the approach i mentioned first and use that macro variable in the input statement

 

for this you need to create the dataset first, and then by dictionary.columns you create a macro variable and then read the file by data step , this time you use the macro variable for the formats as i mentioned in the code.

 

proc sql;
select case when xtype='char' then strip(name)||' $'||strip(put(length+1,best.))||'.' else strip(name)||' '||strip(put(length,best.))||'.' end into: len from dictionary.columns where libname='SASHELP' and memname='CLASS';
quit;

    data TEST                                        ;
    %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
    infile '~path\test.txt' delimiter = ' ' MISSOVER DSD lrecl=32767 firstobs=2 ;
       informat Obs best32. ;
       informat VAR2 $1. ;
       informat VAR3 $1. ;
       informat VAR4 $1. ;
       informat Name $5. ;
       informat VAR6 $1. ;
       informat VAR7 $1. ;
       informat VAR8 $1. ;
       informat VAR9 $1. ;
       informat VAR10 $1. ;
       informat VAR11 $1. ;
       informat Sex $1. ;
       informat VAR13 $1. ;
       informat VAR14 $1. ;
       informat VAR15 $1. ;
       informat Age $1. ;
       informat VAR17 $1. ;
       informat VAR18 best32. ;
       informat VAR19 $1. ;
       informat Height $1. ;
       informat VAR21 $1. ;
       informat VAR22 $1. ;
       informat VAR23 best32. ;
       informat Weight $1. ;
       informat VAR25 $1. ;
       informat VAR26 $1. ;
       informat VAR27 $1. ;
       informat VAR28 $1. ;
       informat VAR29 $1. ;
       informat VAR30 best32. ;
       format &len;
      
 input
 Obs
 VAR2 $
 VAR3 $
 VAR4 $
 Name $
 VAR6 $
 VAR7 $
 VAR8 $
 VAR9 $
 VAR10 $
 VAR11 $
 Sex $
 VAR13 $
 VAR14 $
 VAR15 $
 Age $
 VAR17 $
 VAR18
 VAR19 $
 Height $
 VAR21 $
 VAR22 $
 VAR23
 Weight $
 VAR25 $
 VAR26 $
 VAR27 $
 VAR28 $
 VAR29 $
 VAR30

    ;
    if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
    run;
Thanks,
Jag
Super User
Posts: 9,687

Re: How to add extra length (+1) to all variables

You want change the format of character variables or 

change the length of character variables ?

Assuming you want the latter.

 

data class;
 set sashelp.class;
run;


proc sql;
 select catx(' ',name,'char(',length+1,')') into : list 
  separated by ','
  from dictionary.columns
   where libname='WORK' and memname='CLASS' and type='char';
   
 alter table class
 modify &list ;
quit;
Occasional Contributor
Posts: 8

Re: How to add extra length (+1) to all variables

Yes, the latter (length). If I'm forced to import using a datastep I'd have to reformat the format, informat, and input.

Trusted Advisor
Posts: 1,131

Re: How to add extra length (+1) to all variables

Please let me know if the approach i mentioned works once you try it
Thanks,
Jag
Occasional Contributor
Posts: 8

Re: How to add extra length (+1) to all variables

Will do, thanks!

Super User
Posts: 5,093

Re: How to add extra length (+1) to all variables

Really you should be adding 2 instead of 1 to the length.  A variable that contains text might have an entire word truncated, which you might not find out by adding just 1 to the length.

Occasional Contributor
Posts: 8

Re: How to add extra length (+1) to all variables

Hadn't considered that, thank you! I will definitely be changing it to 2.

Super User
Super User
Posts: 7,414

Re: How to add extra length (+1) to all variables

Don't use proc import.  The reason is that what you are doing by this method is letting SAS guess what you want out.  This may work for some of the time but I can guarentee you it wont for a lot of the other time.  The key part to any transfer of data (or in fact any computer related task) is documentation.  Where is your data import agreement?  That would tell you exactly what the strcuture of the data is, maybe what it contains, transfer schedule etc.  From that document you could literally copy 90% of a datastep import program from it.  Its also a fixed, know strcuture then that is transparent to anyone using it.

All this "checking things haven't truncated" type of thing is symptomatic of a broken import process.

Ask a Question
Discussion stats
  • 13 replies
  • 589 views
  • 3 likes
  • 5 in conversation