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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

11 REPLIES 11
Reeza
Super User

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

And are you sure the code above ran correctly?

ngnikhilgoyal
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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.

Haikuo
Onyx | Level 15

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;

ballardw
Super User

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;

ngnikhilgoyal
Calcite | Level 5

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

data test;

    set dataset;

    informat a--hv yn;

    run;

ballardw
Super User

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>

Tom
Super User Tom
Super User

????

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.

ballardw
Super User

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.

Tom
Super User Tom
Super User

That does not have anything to do with the INFORMAT statement.  You cannot reference variables by position (A -- Z) or by prefix (A:) 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.

Ksharp
Super User

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';

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
  • 11 replies
  • 24914 views
  • 7 likes
  • 6 in conversation