topic Re: replace values in dataset for all variables in SAS Procedures

replace values in dataset for all variables

ngnikhilgoyal — Mon, 20 Apr 2015 22:35:12 GMT

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.

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?

Re: replace values in dataset for all variables

Reeza — Mon, 20 Apr 2015 22:50:47 GMT

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

And are you sure the code above ran correctly?

Re: replace values in dataset for all variables

ngnikhilgoyal — Mon, 20 Apr 2015 23:01:31 GMT

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

;

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.

Re: replace values in dataset for all variables

Tom — Mon, 20 Apr 2015 23:24:46 GMT

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.

Re: replace values in dataset for all variables

Haikuo — Tue, 21 Apr 2015 03:02:06 GMT

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

;

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;

Re: replace values in dataset for all variables

Ksharp — Tue, 21 Apr 2015 12:18:36 GMT

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

Re: replace values in dataset for all variables

ballardw — Tue, 21 Apr 2015 15:19:35 GMT

Using Hai.kuo 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

;

run;

Re: replace values in dataset for all variables

ngnikhilgoyal — Tue, 21 Apr 2015 15:35:28 GMT

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

data test;

set dataset;

informat a--hv yn;

run;

Re: replace values in dataset for all variables

ballardw — Tue, 21 Apr 2015 15:43:10 GMT

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>

Re: replace values in dataset for all variables

Tom — Wed, 22 Apr 2015 13:58:22 GMT

????

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.

Re: replace values in dataset for all variables

ballardw — Wed, 22 Apr 2015 15:02:10 GMT

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.

Re: replace values in dataset for all variables

Tom — Wed, 22 Apr 2015 16:44:02 GMT

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.