Macro input didn't work?

[ybz12003]

Hello,

I create a macro variable list that wanna change its format from text to numeric (4 digits).  However, I found the format is still text though the log window showed that it's done.  I don't know what I have done wrong.

proc sql;
	select trim(name) into : FalseChalist separated by ' '  from ARI1617_falsecha;
quit;

%put &FalseChalist;

data ARI1617_format;
	set ARI1617;
	array vars &&FalseChalist.;
    do over vars;
        Vars=input(Vars, best4.);
    end;
run;

proc contents data=ARI1617_format; run;

180 proc sql;
181 select trim(name) into : FalseChalist separated by ' ' from ARI1617_falsecha;
182 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

SYMBOLGEN: Macro variable FALSECHALIST resolves to uairway uallergy uapnea uendo ugastro ugerd
ugestage uprem
183
184 %put &FalseChalist;
uairway uallergy uapnea uendo ugastro ugerd ugestage uprem
185
186 data ARI1617_format;
187 set ARI1617;
188 array vars &FalseChalist.;
SYMBOLGEN: Macro variable FALSECHALIST resolves to uairway uallergy uapnea uendo ugastro ugerd
ugestage uprem
189 do over vars;
190 Vars=input(Vars, best4.);
191 end;
192 run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
190:9
NOTE: There were 1201 observations read from the data set WORK.ARI1617.
NOTE: The data set WORK.ARI1617_FORMAT has 1201 observations and 50 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds

[ballardw]

Once a SAS variable exists the type is set. You cannot change the type.

My personal suggestion if you changing that many variables is to go back to the step where you read the data into SAS and control it there as it may be the easiest.

Otherwise you have to create new variables.

For a single variable the approach can look like this:

data want; 
    set have (rename=(var=oldvar));
    var = input(oldvar, 4.);
    drop oldvar;
run;

Since you could use an array of new variables this would be the approach (Note: Do Over doesn't always work as expected with TWO arrays involved and since it isn't even in the documentation I would suggest not using it habitually.)

proc sql;
	select trim(name) into : FalseChalist separated by ' '  from ARI1617_falsecha;
quit;
/* this should have the number of variables if your data is correct*/
%let varcount = &sqlobs;

%put &FalseChalist;

data ARI1617_format;
	set ARI1617;
	array old (*) &FalseChalist.;
        array newvar (&sqlobs);
    do i=1 to dim(old);
        Newvar[i]=input(old[i], best4.);
    end;
   drop i  &FalseChalist.;
run;

You could use a similar Proc SQL step to create macro variable to create the NEW names. Or a rename clause for use in a data step option similar to my first example.

I note that once again the code posted is not the code you actually ran. Note that you showed &&FalseChaList in the ARRAY statement but your log shows &FalseChalist. If you had used that && there would have been unreferenced variable errors.

---

@ybz12003 wrote:

Hello,

I create a macro variable list that wanna change its format from text to numeric (4 digits).  However, I found the format is still text though the log window showed that it's done.  I don't know what I have done wrong.

proc sql;
	select trim(name) into : FalseChalist separated by ' '  from ARI1617_falsecha;
quit;

%put &FalseChalist;

data ARI1617_format;
	set ARI1617;
	array vars &&FalseChalist.;
    do over vars;
        Vars=input(Vars, best4.);
    end;
run;

proc contents data=ARI1617_format; run;

180 proc sql;
181 select trim(name) into : FalseChalist separated by ' ' from ARI1617_falsecha;
182 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

SYMBOLGEN: Macro variable FALSECHALIST resolves to uairway uallergy uapnea uendo ugastro ugerd
ugestage uprem
183
184 %put &FalseChalist;
uairway uallergy uapnea uendo ugastro ugerd ugestage uprem
185
186 data ARI1617_format;
187 set ARI1617;
188 array vars &FalseChalist.;
SYMBOLGEN: Macro variable FALSECHALIST resolves to uairway uallergy uapnea uendo ugastro ugerd
ugestage uprem
189 do over vars;
190 Vars=input(Vars, best4.);
191 end;
192 run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
190:9
NOTE: There were 1201 observations read from the data set WORK.ARI1617.
NOTE: The data set WORK.ARI1617_FORMAT has 1201 observations and 50 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds

---

[PaigeMiller]

Oh, I see what is going on, thanks @ballardw 

Despite the subject line of this thread "Macro input didn't work", this has nothing to do with macros (there are no macros in this code) and it has nothing to do with the usage of the macro variable. 

@ybz12003 you should not jump to the conclusion that the problem is due to your macro variable.

In fact, the usual advice here is that you get the code to work without macro variables first, and then you add in macro variables as needed. Had you done that, you would have not had the problem with macro variables, and your code would work properly. Remember, if you don't have working code first, it will never work with macro variables.

[ybz12003]

I can't use the newvar names after formating the dataset, because I can't disguise which ones they are. How to keep my old var names.

[PaigeMiller]

---

@ybz12003 wrote:
I can't use the newvar names after formating the dataset, because I can't disguise which ones they are. How to keep my old var names.

---

proc sql;
	select trim(name) into : FalseChalist separated by ' '  from ARI1617_falsecha;
	select cats('num_',name) into : FalseChalist1 separated by ' '  from ARI1617_falsecha;
quit;

%put &FalseChalist;

data ARI1617_a;
	set ARI1617;
	array vars $ &FalseChalist.;
    array numvars &FalseChalist1;
    do i=1 to dim(vars);
        numvars(i)=input(Vars(i), best4.);
    end;
    drop i;
run;

Also, let's get the terminology correct, this does not have anything to do with formats. Formats are not the issue here. This is creating a new variable with a different variable type (numeric or text).

[ybz12003]

Thanks, Tom.  I use you as a reference, below is mine.  I added rename step in the next datastep.

proc sql;
	select trim(name) into : FalseChalist separated by ' '  from ARI1617_falsecha;
	select cats('num_',name) into : FalseChalist1 separated by ' '  from ARI1617_falsecha;
quit;
%put &FalseChalist;
%put &FalseChalist1;

data ARI1617_format;
	set ARI1617;
    array vars $ &FalseChalist.;
    array numvars &FalseChalist1;
    do i=1 to dim(vars);
        numvars(i)=input(Vars(i),2.);
    end;
    drop i &FalseChalist.;
run;
proc contents data=ARI1617_format out=ARI1617formatContent; run;

data ARI1617formatUpdate;
	set ARI1617_format;
	array numvars &FalseChalist1.;
	array vars &FalseChalist.;
	do i=1 to dim(numvars);
		rename=vars[i]=numvars[i];
		format &FalseChalist. Best.;
	end;
	drop i &FalseChalist1; 
run;
proc contents data=ARI1617formatUpdate out=ARI1617updateCon; run;

[Tom]

This line makes no sense.

		rename=vars[i]=numvars[i];

You are creating a variable named RENAME that is going to be set to a boolean result.  So it will be set to 1 when the two values referenced by indexing into the two arrays are equal and zero otherwise.  You have it in a do loop so only the result of the final comparison will remain since it will overwrite the values stored into RENAME by the passes through the DO loop.

If you want RENAME a varaible you need to use RENAME statement instead. (Or the RENAME= dataset option).

The syntax for the RENAME statement requires actual variable names.  Not array index references.

[Tom]

Please read through earlier code carefully and consider the meaning of each line.

Spoiler

[ybz12003]

Hmm, I got it. Thanks.

[Kurt_Bremser]

Since you did not supply example data, I have to make guesses.

My guess is that you want to convert character variables to numeric. Since one cannot change the type of an existing variable, you have to create new ones and do the "rename-convert-drop" dance.

You are alerted to the fact that you cannot change the type by this:

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
190:9

This refers to

190 Vars=input(Vars, best4.);

and if you count the columns in your original log (not the one that was mangled by you posting into the main window; we have VERY OFTEN told you now to use the code box opened with the </> button so that the formatting is kept), you will find that the re-conversion to character happens when the result of the INPUT function (which is numeric) has to be converted to character to store it in the variable that is still character.

So, your statement that the log shows that the conversion was done is in fact patently wrong.

Changing the type of a single variable is done like this:

data changed;
set unchanged (rename=(
  var = _var
));
var = input(_var,4.);
drop
  _var
;
run;

By expanding the RENAME= dataset option and the DROP statement, and adding additional assignment statements, you can do that for all of your variables; this can be done in a macro.

One nice method to change a list of variables without a macro is to use a double transpose:

data wrong;
input id $ a $ b $ c $;
datalines;
A 2004 2005 2006
B 2005 2006 2007
;

proc transpose
  data=wrong
  out=long_wrong
;
by id;
var a b c; /* you only need to expand this statement for more variables */
run;

data long_right;
set long_wrong;
col = input(col1,4.);
run;

proc transpose
  data=long_right
  out=want (drop=_name_)
;
by id;
id _name_;
var col;
run;

[ybz12003]

Yes. You have OFTEN told me the rule, and I keep forgetting them if I don't use them frequently. Sorry...

[ybz12003]

Thanks for all the great advise.

[Tom · Accepted solution]

It is a little confusing as to what the goal is here.  You claim you want to convert from CHAR to NUM.  But the code is using BEST.  That is the name of a FORMAT.  If you want to convert from character to numbers you need to use an INFORMAT.  Since the input() function does not care if the width used on the informat is larger than the length of the string being read just use 32. as the informat.  Or if the strings have thousands separators or dollar signs use COMMA32. informat.

You will have to make new variables as you cannot change the type of an existing variable.

proc sql noprint ;
select nliteral(name)
     , nliteral(cats('__',name))
     , catx('=',nliteral(name), nliteral(cats('__',name))
  into :FalseChalist separated by ' '  
     , :templist separated by ' '
     , :renamelist separates by ' '
from ARI1617_falsecha
;
quit;

data ARI1617_format;
  set ARI1617(rename=(&renamelist));
  array good &falsechalist ;
  array bad &templist;
  do over good;
    good=input(left(bad),32.);
  end;
  drop &templist;
run;

[ybz12003]

I'm sorry that I have difficulty digesting many suggestions in a short time.  I try to use the ones I understand and incorporate them into my program.  Still, thanks for all of your great advice.