Help using Base SAS procedures

Format Issue

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Format Issue

Hi,

     I am trying to convert some $12. type variables into best12. format variables, with obs. in some variables all empty.

     But for some variables, all available formats are $ types, say, $, $ascii. And my trying in datasteps to format did not work.
    

      data a;

          set b;

          informat _COL2-_COL50 best12.;

          format _COL2-_COL50 best12.;

     run;

     Can someone please help. Thanks!


Accepted Solutions
Solution
‎10-27-2011 10:01 AM
PROC Star
Posts: 7,363

Re: Format Issue

Depends upon what kind of data you are importing.  If you are using proc import, I don't think you can control it.  If it is a spreadsheet, I think you are stuck.  If it is a text or delimited file, you can import it correctly, the first time, by using a datastep.

Regardless, you can still accomplish the revision .. just a bit harder.  Given your example data, the following should work:

data b;

  retain _COL2-_COL25 (24*"123")

         _COL26-_COL50 (25*321);

  output;

run;

data a (keep=_col2-_col50);

  set b (rename=(_col2-_col50=old2-old50));

  array oldchar(*) _character_;

  array oldnumb(*) _numeric_;

  array _col(50);

  do i=1 to dim(oldchar);

    _col(compress(vname(oldchar(i)),,'kd'))=

     input(oldchar(i),best12.);

  end;

  do i=1 to dim(oldnumb);

    _col(compress(vname(oldnumb(i)),,'kd'))=

     oldnumb(i);

  end;

run;

View solution in original post


All Replies
PROC Star
Posts: 7,363

Format Issue

You can't change a variable's type from character to numeric.  You have to create new variables.

You could accomplish what you want by using something like:

*create test data file;

data b;

  retain _COL2-_COL50 (49*"123");

  output;

run;

data a (drop=i old: _col1);

  set b (rename=(_col2-_col50=old2-old50));

  array old(49) old:;

  array _col(50);

  format _col: best12.;

  do i=1 to 49;

    _col(i+1)=input(old(i),best12.);

  end;

run;

Contributor
Posts: 60

Re: Format Issue

Thank you!

I tried this.  But anther problem comes.

Within these variables, _COL2-_COL50, some of them are numeric, so I can not use array.

Is there any soultions for this?

Or, is it possiable for me to control the input type at the beginning, in the import procedure when I read the xls file.

THX!

Solution
‎10-27-2011 10:01 AM
PROC Star
Posts: 7,363

Re: Format Issue

Depends upon what kind of data you are importing.  If you are using proc import, I don't think you can control it.  If it is a spreadsheet, I think you are stuck.  If it is a text or delimited file, you can import it correctly, the first time, by using a datastep.

Regardless, you can still accomplish the revision .. just a bit harder.  Given your example data, the following should work:

data b;

  retain _COL2-_COL25 (24*"123")

         _COL26-_COL50 (25*321);

  output;

run;

data a (keep=_col2-_col50);

  set b (rename=(_col2-_col50=old2-old50));

  array oldchar(*) _character_;

  array oldnumb(*) _numeric_;

  array _col(50);

  do i=1 to dim(oldchar);

    _col(compress(vname(oldchar(i)),,'kd'))=

     input(oldchar(i),best12.);

  end;

  do i=1 to dim(oldnumb);

    _col(compress(vname(oldnumb(i)),,'kd'))=

     oldnumb(i);

  end;

run;

PROC Star
Posts: 7,363

Re: Format Issue

Conversely, although I've never tried it, in a different thread Doc mentions a new import tool in EG that can override the import procedure's guessing of what format to use (see: http://communities.sas.com/message/108155#108155 ).

If he is correct, I wonder why the capability hasn't been added to the import procedure as well.

Contributor
Posts: 60

Re: Format Issue

I will have a try. THX.

Contributor
Posts: 60

Re: Format Issue

Many thanks. I have got it.

It takes time for me on using the code.

Thanks again.

Contributor
Posts: 60

Re: Format Issue

Hi Art,

     I am try to label these new variables with the same the labels of old ones.

     I try "call label", but it does not work, I don't know why. Here it is,

    

    %let j=2;

    %let k=1;

    do i= 1 to &nvar;

        %let j=%eval(&j+1);

        %let k=%eval(&k+1);

        %let lab=%sysfunc(varlabel(&open,&j)));

        label COL&k="&lab";

    end;

PROC Star
Posts: 7,363

Re: Format Issue

I just responded to this code in a different thread.  Now I remember having seen it a few days ago.

If you are going to use macro variables, you really ought to do some reading first, especially concerning how they work and the necessary timing of when they are processed.  The general rule is that you can't create and use them within the same datastep.

Second, it looks like you are trying to create variable names that only contain numbers.  That doesn't conform with the SAS variable name rules.  You might be able to get around it by setting the SAS system option validvarname=any, but I would recommend that you simply use variable names that do conform with the rules.

I think you will get the best advice if you simply show an example of what you currently have and what you want to end up with.

Contributor
Posts: 60

Re: Format Issue

Thank you for your advice, really.

I start coding not long, and alway meet many problems.

Is there any book under your recommandation? I am really not used to the SAS guide.

PROC Star
Posts: 7,363

Re: Format Issue

My answer will sound sarcastic: read as much as you can find time to read.

Many recommend the little SAS book as a starter, but I recommend reading ALL of the documentation, ALL of the SAS press books, ALL of the global and region user group papers, and ALL of the posts that are on the forum and other boards like SAS-L.

Unfortunately, there isn't time to read all of that and your needs might be more specific.

If it is just the macro language that you want to read up on for now take a look at:

http://books.google.ca/books?id=uTFRZSiRZm0C&pg=PA482&lpg=PA482&dq=books+papers+sas+macro&source=bl&...

Super User
Super User
Posts: 6,502

Re: Format Issue

I usually avoid trying to loop through observations and instead apply SAS operations to full sets.

You can pull the metadata for your original file and use it to generate the new variables.

You can view your conversion step as this simple data step.

data want;

  &attrib;

  array _char $32 &oldchar ;

  array _num &newnum;

  set have (rename=(&rename)) ;

  do over _char;

    _num=input(_char,best32.);

  end;

  drop &oldchar ;

run;

Where

ATTRIB contains a series of ATTRIB statements to set the variables names, type, length, format, label etc .

RENAME contains old=newname pairs that will rename the old character variables to unique temporary names so that new numeric variables with those names can be created.

OLDCHAR contain the renamed character variables that need to be converted to numeric.

NEWNUM contain the names of the variables to convert from numeric to character.

For example if you want to convert all character variables to numeric then you can generate those four list variables with something like:

proc sql noprint ;

  create table vardef as select * from dictionary.columns

    where libname='WORK' and memname='A'

  ;

  select catx(' ','attrib',name,'length=8','label=',quote(trim(label)||' '))

       , varnum

    into :attrib separated by ';'

       , :dummy

    from vardef

    order by varnum

  ;

  select name

       , catx('=',name,'_'||name)

       , '_'||name

    into :newnum separated by ' '

       , :rename separated by ' '

       , :oldchar separated by ' '

    from vardef

  ;

quit;

For a more complicated situation you might need to use more complicated queries to generate ATTRIB (especially for length and format attributes) or to subset the variables included in the other three macro variables.

Contributor
Posts: 60

Re: Format Issue

THX, I am learning your code. It is not easy for me.

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 254 views
  • 5 likes
  • 3 in conversation