How to set value of 0 after transposing

[amyk]

Hi,

I am having a bit of an issue trying to figure out how can I set a zero value to the data after transposing. 

After I transposed the data I have over 1200 variables that blank and I want to set them as zero with out having to write out all variables. 

I attempted using an array however that failed on me.

Please Help Me!!!

[ed_sas_member]

Hi @amyk 

Happy new year!

Could you please share a sample of your data?

2 options come into mind mind:

• If all the transposed data (and only them) are numeric, you can mention them in an array as _numeric_
• If the name of all the new columns start with the same prefix (let's say 'col'), you can mention them in an array by using col:

[amyk]

Hi Ed

Thanks for the quick response. After Transposing the variables don't have the same prefix. Below is what I had started to do. 

the code blow intention was to have an array to contain all the variables and set them a value of zero the variables that don;t have a value. 

data CheckboxFinal ;
set CheckBoxCount ;
array varlist{*} TotalCheckBox;
do i = 1 to dim(varlist) ;
if varlist{i} = . then varlist{i} = 0 ;

end ;

drop _NAME_ i ;

run ;

[Criptic]

Since I didn't have any sample data I constructed this simple example but it should work just fine for you. Just replace the variable definition with your set statement

data work.result(drop=i);
 x = .;
 p = 6;
 array a{*} _numeric_;
 do i = 1 to dim(a);
  if a{i}=. then a{i}=0;
 end;
run;

[Tom]

That code is only testing the one variable named TotalCheckBox, which you could more easily do without the array.

The array definition needs to include all of the variables that you wish to apply the changes to.

[amyk]

Thats the thing is that i have over 1200 variables to loop through and set the zero. However I am trying to find away instead of hard coding all the variables 

[Criptic]

Just use the _numeric_ and _character_ keywords like I did in my example. That will create an array with all numeric/character variables

[unison]

Here's a couple ideas/approaches.

data have;
	infile datalines missover;
	input id a1-a1000;
	datalines;
1
2
3
;
run;

*Option 1 - Vars are arranged in a numeric sequence;
* -- (or side-by-side: replace a1-a1000 with a1--a1000);
data want;
	set have;
	array myarr a1-a1000;

	do over myarr;
		if myarr=. then
			myarr=0;
	end;
run;

*Option 2 - Select desired variables into a macro variable;
proc means data=have nolabels NMISS stackodsoutput;
	var _NUMERIC_;
	ods output Summary=MissingValues;
run;
proc sql noprint;
	select variable into :myvars separated by " " 
	from missingvalues 
	where nmiss>0 /*or specify variable in (...) list*/
	;
quit;

data want;
	set have;
	array myarr &myvars;

	do over myarr;
		if myarr=. then
			myarr=0;
	end;
run;

You can also make use of dictionary.columns within proc sql to get the desired list of variables into a macro variable.

Cheers

[ballardw]

---

@amyk wrote:

Hi,

I am having a bit of an issue trying to figure out how can I set a zero value to the data after transposing. 

After I transposed the data I have over 1200 variables that blank and I want to set them as zero with out having to write out all variables. 

I attempted using an array however that failed on me.

Please Help Me!!!

---

Perhaps this is an indication that Transpose wasn't really needed.  Or perhaps you should use a different approach to the transpose.

Can you show a small example of the input data and what you need it to look like after the transpose. (Be prepared to answer questions about why the data needs that transposed structure.)

[Ksharp]

proc stdize data=have out=want missing=0 reponly;
var _numeric_;
run;