- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Please post the full code that generated the error - including the proc transpose.
And are you sure the code above ran correctly?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks. Can I apply the informat to the dataset like this :
data test;
set dataset;
informat a--hv yn;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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>
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
????
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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';