Help using Base SAS procedures

replace values in dataset for all variables

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

replace values in dataset for all variables

I have a data set in which there are variables named a b c etc up to hv.

The values of the obs are "Y" and "N". Y means a 1 and N means a 0. I wanted to replace all the Y and N with 1 and 0.


data test;

   input a b c d e f g h i j k l;

   datalines;

/*Y and N values*/

;

I tried this :


data test;

   set inputds;

   array change a--hv;

            do over change;

            if change= 'Y' then change = 1;

            if change = 'N' then change = 0;

            end;

   run ;

This seems to have done the work but when I do a proc transpose on the output data or do a loop on the variables , I get the message :

     133

ERROR 133-185: A loop variable cannot be an array name or a character variable;  It must be a

          

scalar numeric.

or

NOTE: No variables to transpose.

NOTE: There were 24130 observations read from the data set WORK.TEST.

NOTE: The data set WORK.WANT has 0 observations and 24131 variables.

I checked the column attribute after converting the Y into 1 and N into 0 and the format says character instead of numeric.

How can I correct this?


Accepted Solutions
Solution
‎04-21-2015 11:19 AM
Super User
Posts: 11,343

Re: replace values in dataset for all variables

Using idea but a bit earlier:

proc format;

     invalue yn

           'Y'=1

           'N'=0

     ;

run;

data test;

  informat a  b  c  d  e  f  g  h  I  j  k  l yn. ; /* this will READ the Y/N to 1 0 at the beginning. No reason to play with conversion. ALSO since you have a variable "I" no NOT use i as a loop counter, use Do LoopCount or similar */

   input a  b  c  d  e  f  g  h  I  j  k  l ;

   datalines;

Y N N N N N Y N N Y N N

Y N N N N N Y N N Y N N

Y N N N N N Y N N Y N N

;

run;

View solution in original post


All Replies
Super User
Posts: 19,772

Re: replace values in dataset for all variables

Posted in reply to ngnikhilgoyal

Please post the full code that generated the error - including the proc transpose.

And are you sure the code above ran correctly?

Contributor
Posts: 40

Re: replace values in dataset for all variables

Here's the full code :

Adding some data:

data test;

   input a $ b $ c $ d $ e $ f $ g $ h $ i $ j $ k $ l $;

   datalines;

Y N N N N N Y N N Y N N

Y N N N N N Y N N Y N N

Y N N N N N Y N N Y N N

;

data test2;

   set test;

   array change a--l;

            do over change;

            if change= 'Y' then change = 1;

            if change = 'N' then change = 0;

            end;

   run ;

proc format;

invalue groupfour

'1000'=4

'0100'=3

'0010'=2

'0001'=1

;

run;

proc sql noprint;

     select name, count(*)/4 into : varlist separated by ' ' , :varcount

     from dictionary.columns where libname='WORK'

          and memname='TEST2';

quit;

%let varcount = %left(&varcount);

data want;

set test2;

array group &varlist;

array newvar  Var1 - Var&varcount;

do I = 1 to &varcount;

     base = (I-1)*4;

     newvar =  input(cats(group[base+1],group[base+2],group[base+3],group[base+4]),groupfour.);

end;

drop I base;

run;

65   do I = 1 to &varcount;

        -

        133

ERROR 133-185: A loop variable cannot be an array name or a character variable;  It must be a scalar

               numeric.

proc transpose data = test2 out = test2_t;

run;

NOTE: No variables to transpose.

NOTE: There were 24130 observations read from the data set WORK.TEST.

NOTE: The data set WORK.TEST_T has 0 observations and 24131 variables.

NOTE: PROCEDURE TRANSPOSE used (Total process time):

      real time           0.21 seconds

      cpu time            0.21 seconds

Message was edited by: Nikhil Goyal - I have added a little sample data to make the error easy to replicate.

Super User
Super User
Posts: 7,039

Re: replace values in dataset for all variables

Posted in reply to ngnikhilgoyal

Since you defined I as a character in your initial dataset you cannot use I as the index variable for your DO loop.

Use a different variable name for your DO loop.

For your PROC TRANSPOSE you need to list the variables to transpose in a VAR statement.  Otherwise it will only try to transpose the numeric variables.

Respected Advisor
Posts: 3,156

Re: replace values in dataset for all variables

Posted in reply to ngnikhilgoyal

One way to play is first to output and then to stream in using informat:

data test;

     input a $ b $ c $ d $ e $ f $ g $ h $ i $ j $ k $ l $;

     datalines;

Y N N N N N Y N N Y N N

Y N N N N N Y N N Y N N

Y N N N N N Y N N Y N N

;

proc format;

     invalue yn

           'Y'=1

           'N'=0

     ;

run;

filename _t temp;

data _null_;

     set test;

     length varname $ 10;

     file _t dsd;

     put (_all_) (~);

     if _n_=1 then

           do;

                call execute ('data want; infile _t dsd; input (');

                do while (1);

                      call vnext(varname);

                      if varname eq 'varname' then

                           leave;

                      call execute(varname);

                end;

                call execute(')'||'(:yn1.);run;');

           end;

run;

Solution
‎04-21-2015 11:19 AM
Super User
Posts: 11,343

Re: replace values in dataset for all variables

Using idea but a bit earlier:

proc format;

     invalue yn

           'Y'=1

           'N'=0

     ;

run;

data test;

  informat a  b  c  d  e  f  g  h  I  j  k  l yn. ; /* this will READ the Y/N to 1 0 at the beginning. No reason to play with conversion. ALSO since you have a variable "I" no NOT use i as a loop counter, use Do LoopCount or similar */

   input a  b  c  d  e  f  g  h  I  j  k  l ;

   datalines;

Y N N N N N Y N N Y N N

Y N N N N N Y N N Y N N

Y N N N N N Y N N Y N N

;

run;

Contributor
Posts: 40

Re: replace values in dataset for all variables

Thanks. Can I apply the informat to the dataset like this :

data test;

    set dataset;

    informat a--hv yn;

    run;

Super User
Posts: 11,343

Re: replace values in dataset for all variables

Posted in reply to ngnikhilgoyal

The informat statement is limited to numbered lists such as :

Informat var1-v100;

This is because Informat has the ability to create variables. A list like a-hv while you think is clear doesn't really differentiate from variables aa aaa aaaa ab abba etc as they are all in the textual interval of A to HV. This is similar to the input statement.

HOWEVER your can specify a default informat which is used for all variables not explicitly given one.

data want;

     informat default=yn.;

     input a b c d e ;

<etc>

Super User
Super User
Posts: 7,039

Re: replace values in dataset for all variables

????

There is nothing about an INFORMAT statement that limits the types of variable lists you can use.

If you want to define the type and length of your variables you should use a LENGTH or ATTRIB statement rather than depending on side effects from FORMAT, INFORMAT, INPUT and other statements.

Super User
Posts: 11,343

Re: replace values in dataset for all variables

Tom

I'm referring to this behavior, at least in SAS 9.2:

1    data junk;
2       informat a-hv $5.;
ERROR: Missing numeric suffix on a numbered variable list (a-hv).
3    run;

But lists such as A1-A10 don't have problems.

Super User
Super User
Posts: 7,039

Re: replace values in dataset for all variables

That does not have anything to do with the INFORMAT statement.  You cannot reference variables by position (A -- Z) or by prefix (ASmiley Happy when they are not defined yet.  You can specify a list of variables with  V1-V100 syntax when they have not yet been defined since SAS knows that syntax completely defines the list of names.

Super User
Posts: 10,023

Re: replace values in dataset for all variables

Posted in reply to ngnikhilgoyal

One side problem is var are character type .so you need quote around them .

if change= 'Y' then change = '1';

if change = 'N' then change = '0';

🔒 This topic is solved and locked.

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

Discussion stats
  • 11 replies
  • 7529 views
  • 6 likes
  • 6 in conversation