BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rpg163
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

12 REPLIES 12
art297
Opal | Level 21

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;

rpg163
Calcite | Level 5

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!

art297
Opal | Level 21

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;

art297
Opal | Level 21

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.

rpg163
Calcite | Level 5

I will have a try. THX.

rpg163
Calcite | Level 5

Many thanks. I have got it.

It takes time for me on using the code.

Thanks again.

rpg163
Calcite | Level 5

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;

art297
Opal | Level 21

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.

rpg163
Calcite | Level 5

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.

art297
Opal | Level 21

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&...

Tom
Super User Tom
Super User

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.

rpg163
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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