Is there a way to export dataset values into a macro?

[Tpham]

Have: I have a CSV file, some variables are coded as numeric, some are coded as character in the import.

Want: to automate conversion to variables to numeric using an array. 

If I have a dataset with just the variables names, is there a way to export it automatically into a macro value? See code

data have;
input q1 q2 $ q3;
cards;
1	2	3
;

ods output Variables=Have_contents;
proc contents data=have;
run;

*List of all character var;
data Have_contents;
set Have_contents;
where upcase(type)="CHAR";
keep Variable;
run;


%macro chartonum;
data _null_;
set have;
	array varlist {*} q2; /*WANT: to populate list based on Have_contents values instead of typing q2 here*/

	do a=1 to dim(varlist);
		call symput("MVvarlist"||strip(a),strip(vname(varlist(a))));
	end; 

	call symput("mvNB",strip(dim(varlist)));
run;

data have1;
set have;

/*Convert to num*/
%do b=1 %to &mvNB;
	&&MVvarlist&b.._n = input(&&MVvarlist&b, 8.);
%end;

run;

%mend chartonum;

%chartonum;



 

[PaigeMiller]

There is no automatic way.

It's really hard to see why you want to take a well-known and useful format for data (the SAS data step) and turn this into a much less useful format such as macro variables. Yes, there is a way, but I think this is a case where just because it COULD be done doesn't mean it SHOULD be done. Why do you want to do this?

[SASKiwi]

IMO it is way better to get variable types correct when you are importing, not afterwards. DATA step CSV imports will do this. You would then save yourself the hassle of conversion.

[Tom]

I think you are making this way too hard. In a number of ways.

First if you have CSV file then you have a file with text in it. If you want to read all of the variables as numeric then just read them as numeric to begin with.  

data want;
  infile 'myfile.csv' dsd truncover firstobs=2;
  input var1-var20;
run;

But let's ignore that for now and assume that you have datasets that someone else gave you and you just want to convert all of the character variables into numeric variables.  To convert them you might run a data step like this:

data want ;
  set have ;
  new_var1 = input(var1,??32.);
  new_var2 = input(var2,??32.);
  rename
    new_var1=var1 var1=old_var1
    new_var2=var2 var2=old_var2
  ;
run;

To generate that code you just need the list of character variables, and also a method for generating the NEW (or temporary) names you need be able have two variables (a numeric version and character version).  There are many ways to generate wall paper code like that. One way is to get the list of names in a dataset and use a data _null_ step to generate the code.  Either by writing (putting) the code to a file and %including it, or by using CALL EXECUTE() to generate the code.

Another way to approach the problem is just use PROC TRANSPOSE to get all of the character values into a single variable.  For that to work you will need a set of keys that uniquely identify the rows in the source data.  If you don't have that then just make a new variable to serve that role.

data step1;
  row+1;
  set have (keep=_character_);
run;
proc transpose data=step1 out=step2 ;
  by row;
  var _character_;
run;
data step3 ;
  set step2 ;
  value = input(col1,??32.);
run;
proc transpose data=step3 out=step4 (drop=_name_);
  by row;
  id _name_;
  var value;
run;

data want ;
  set have (drop=_character_);
  set step4 (keep=_character_);
run;

[TomKari]

Unless it's a really trusted source (ME!) I read CSV files as follows:

1. First, pull in every column as a text variable.
2. Edit each variable according to what I expect.
3. Where I want a numeric variable, convert it under my control.

Tom