DATA Step, Macro, Functions and more

Length of variable in Excel file

Reply
Contributor
Posts: 40

Length of variable in Excel file

I am importing several excel files-all in the same format so that I can then set them all one below the other in a single dataset. Thes ample name has values like A1, B2, etc. It only runs upto A9 (total of 2 characters) in one file but has more than 2 characters in some other excel files. When I use the "set" statement to set all these datasets to a single dataset, the sample name gets truncated to 2 characters. How do I make sure I retain the entire name and also uniformity in the variable length?
Thanks
SAS Super FREQ
Posts: 8,865

Re: Length of variable in Excel file

Hi:
If you know what LENGTH you want, then you can simply put a LENGTH statement before your SET statement. The program below shows the creation of datasets A and B with variables of differing lengths. Note how the PROC CONTENTS after the SET, shows that the new file uses the LENGTH from the LENGTH statement.

Technically, you only need a LENGTH statement for your character variables, since the numeric variables will all have a length of 8 by default, but I wanted to show the difference in how you would set a length for a numeric variable if you needed to. Without an explicit LENGTH statement, SAS will use the LENGTH from the dataset that is listed first on the SET statement -- this can result in the truncation that you describe. Using an explicit LENGTH statement avoids any uncertainty about what the final length will be for the variables. You might control this by putting the dataset with the longest length first -- but it would need to have the longest length for ALL the variables for this approach to work.

cynthia
[pre]
data a;
infile datalines;
input name $ numvar;
return;
datalines;
a2 1111
b1 2222
c3 3333
;
run;

proc contents data=a;
title 'Dataset A';
run;

data b;
infile datalines;
input name $11. numvar;
return;
datalines;
x1xyzxxxx33 333.3
y2pqryyyy44 444.44
z3stuzzzz55 555.575
;
run;

proc contents data=b;
title 'Dataset B';
run;

data together;
length name $20 numvar 8;
set a
b;
run;

proc contents data=together;
title 'Use Lengths from LENGTH statement';
run;

proc print data=together;
title 'Show Datasets Concatenated';
format numvar comma12.3;
run;
[/pre]
Contributor
Posts: 40

Re: Length of variable in Excel file

Posted in reply to Cynthia_sas
Hi Cynthia,

Thank you very much for the detailed response with the code! Putting the dataset with the longest variable length first in the list of datasets in the "set" statement worked for me this time, but I am sure I will use the length statement the next time. Thanks for showing the difference with the code!
Ask a Question
Discussion stats
  • 2 replies
  • 175 views
  • 0 likes
  • 2 in conversation