DATA Step, Macro, Functions and more

Change column width

Accepted Solution Solved
Reply
Regular Contributor
Posts: 216
Accepted Solution

Change column width

Hi

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


Accepted Solutions
Solution
‎11-21-2012 10:23 AM
Super User
Posts: 5,095

Re: Change column width

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.

View solution in original post


All Replies
PROC Star
Posts: 7,364

Re: Change column width

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;

1

2

3

;

data want (drop=_Smiley Happy;

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

  x=put(_x,1.);

run;

PROC Star
Posts: 7,364

Re: Change column width

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=_Smiley Happy;

  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.

Regular Contributor
Posts: 216

Re: Change column width

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;

PROC Star
Posts: 7,364

Re: Change column width

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;

Solution
‎11-21-2012 10:23 AM
Super User
Posts: 5,095

Re: Change column width

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.

Regular Contributor
Posts: 216

Re: Change column width

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

PROC Star
Posts: 7,364

Re: Change column width

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 **************/

Contributor
Posts: 29

Re: Change column width

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.

Regular Contributor
Posts: 216

Re: Change column width

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

PROC Star
Posts: 7,364

Re: Change column width

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1148 views
  • 3 likes
  • 4 in conversation