04-19-2016 07:47 PM - edited 04-19-2016 09:08 PM
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!
04-19-2016 08:52 PM - edited 04-19-2016 09:07 PM
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.
04-19-2016 09:10 PM
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.
04-19-2016 09:15 PM
04-19-2016 09:26 PM
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?
04-19-2016 09:47 PM
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;
04-19-2016 09:40 PM
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;
04-19-2016 10:49 PM
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.
04-20-2016 04:43 AM
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.