topic Re: Change column width in SAS Programming

Change column width

Paul_NYS — Tue, 20 Nov 2012 19:00:25 GMT

I have a data step where I am re-writing the value names in certain columns. I have one column that stored numbers in it (1-5) in it that I changed from numbers to alpha values. However, when I run the data step, the new alpha values are not appearing, only a period. Is there a way to change this from an apparent numeric column to an alpha column?

The second issue is that I am changing the values of column that has alpha characters in it which are 3 char long. I am changing these to longer than 3 characters and the new value names are truncating at 3 characters. Is there a way to increase the length of a column?

Also, this has nothing to do with the above, but is there a way to turn off the email notifications for this forum? I am getting notified for all activity and not just my own--none of the config changes I have done change this.

Paul

Re: Change column width

art297 — Tue, 20 Nov 2012 19:40:49 GMT

Paul, You can't change a variable from character to numeric. I think you are trying to do something like:

data have;

input x;

cards;

;

data want (drop=_:);

set have (rename=(x=_x));

x=put(_x,1.);

run;

Re: Change column width

art297 — Tue, 20 Nov 2012 19:44:29 GMT

Sorry, here is some code that addresses both of your questions:

data have;

length y $3;

input x y $;

cards;

1 aaa

2 bbb

3 ccc

;

data want (drop=_:);

length y $4;

set have (rename=(x=_x));

x=put(_x,1.);

if _n_ eq 2 then y='bbbb';

run;

As for your third question, click on your name at the top right corner and adjust your settings. You can turn off some and/or all of the email notifications.

Re: Change column width

Paul_NYS — Wed, 21 Nov 2012 15:06:28 GMT

When I use the length statement just on the one variable I want to change the length for (exit), it puts that variable first in the resulting data set--which is not the correct order. When I use the length statement on all variables to order them also, the DistributiveNumber and exitMonthTotal variables are blank. Any way to just change the length for the one variable I need to do this for and leave the variable order intact?

Paul

data s1AgeClean2 (rename=COUNT=DistributiveNumber);

length cnty_name $25 startyear 3 exit $ 17 agecat4 3 CohortYearTotal 5 exitMonthTotal 5 DistributiveNumber 5 DistributivePercent 5 CumulativeNumber 5 CumulativePercent 5;

set s1AgeClean;

DistributivePercent=COUNT/CohortYearTotalAge;

CumulativePercent=CumulativeNumber/CohortYearTotalAge;

if exitMonthCategory ne 72;

if exit="XCA" then exit="Adoption";

if exit="XRF" then exit="Reun/Cust/Guard";

if exit="XRM" then exit="Aged Out";

if exit="XOT" then exit="Other Exit";

run;

Re: Change column width

art297 — Wed, 21 Nov 2012 15:19:11 GMT

Paul,

One way to do it is to create a new variable and drop the old one and move the length statement to after the set statement. e.g.:

data s1AgeClean2 (drop=_: rename=COUNT=DistributiveNumber);

set s1AgeClean (rename=(exit=_exit));

length exit $17;

DistributivePercent=COUNT/CohortYearTotalAge;

CumulativePercent=CumulativeNumber/CohortYearTotalAge;

if exitMonthCategory ne 72;

exit=_exit;

if exit="XCA" then exit="Adoption";

else if exit="XRF" then exit="Reun/Cust/Guard";

else if exit="XRM" then exit="Aged Out";

else if exit="XOT" then exit="Other Exit";

run;

Re: Change column width

Astounding — Wed, 21 Nov 2012 15:23:06 GMT

No, you will need to use the approach you have and supply the length for all variables in order.

One problem you have with this code is that the LENGTH statement should not specify a length for DistributiveNumber. From the RENAME option you have, it appears that the actual variable name is COUNT. Only when outputting to the final data set does the name change. Assigning a LENGTH to the wrong variable creates a conflict for SAS: which variable should go into the output data set and be named DistributiveNumber? The one that originally had that name (in the LENGTH statement), or COUNT? It's very likely that the solution would be to change the LENGTH statement to define COUNT instead of DistributiveNumber.

Try that much, and see if it clears up all the problems, or only half.

Good luck.

Re: Change column width

art297 — Wed, 21 Nov 2012 15:35:01 GMT

Paul, My last suggestion would have moved the location of exit to the end of the file. Here is an alternative approach:

data test;

set sashelp.class;

run;

proc sql noprint;

select name into :retains separated by " "

from dictionary.columns

where libname="WORK" and

memname="TEST"

;

quit;

data want;

length sex $6;

set test;

if sex="M" then sex="MALE";

else sex="FEMALE";

run;

data want;

retain &retains.;

set want;

run;

/*********Corrected to add the "separated by" clause on the into statement **************/

Re: Change column width

TimArm — Wed, 21 Nov 2012 15:53:26 GMT

My advice is this: define the layout (I usually use the attrib statement) that you want in a separate file which you can then include before the set statement e.g.

data ds2;

%include programs(layout_ds2);

set ds1;

...

run;

data ds3;

%include programs(layout_ds3);

set ds2 (rename=(dsvar=tmp_dsvar));

...

dsvar = tmp_dsvar;

run;

Does that make sense? You can control drops and keeps in this way too.

Re: Change column width

Paul_NYS — Wed, 21 Nov 2012 18:27:12 GMT

Beside using an 'input' statement, is there another way to define a variable type?

Re: Change column width

art297 — Wed, 21 Nov 2012 18:45:36 GMT

Paul: you don't need an input statement to define a variable type, but you will need it (or a similar operation) in order to populate it. Did you try the method I suggested, namely:

data test;

set sashelp.class;

run;

proc sql noprint;

select name into :retains separated by " "

from dictionary.columns

where libname="WORK" and

memname="TEST"

;

quit;

data want;

length sex $6;

set test;

if sex="M" then sex="MALE";

else sex="FEMALE";

run;

data want;

retain &retains.;

set want;

run;

Re: Change column width

Paul_NYS — Wed, 21 Nov 2012 19:28:38 GMT

I was not using the COUNT in the length statement--that was the problem. Thanks.

And to variable type, all I did was to create a new alpha variable with the same length statement, populate it with the existing values, then drop the old variable. That worked fine.

Paul