topic How to set value of 0 after transposing in SAS Programming

How to set value of 0 after transposing

amyk — Thu, 02 Jan 2020 11:59:52 GMT

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!!!

Re: How to set value of 0 after transposing

ed_sas_member — Thu, 02 Jan 2020 12:30:30 GMT

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:

Re: How to set value of 0 after transposing

amyk — Thu, 02 Jan 2020 12:48:17 GMT

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 ;

Re: How to set value of 0 after transposing

Criptic — Thu, 02 Jan 2020 13:06:22 GMT

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;

Re: How to set value of 0 after transposing

Tom — Thu, 02 Jan 2020 13:22:53 GMT

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.

Re: How to set value of 0 after transposing

amyk — Thu, 02 Jan 2020 13:31:46 GMT

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

Re: How to set value of 0 after transposing

Criptic — Thu, 02 Jan 2020 13:35:04 GMT

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

Re: How to set value of 0 after transposing

unison — Thu, 02 Jan 2020 14:04:30 GMT

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

Re: How to set value of 0 after transposing

ballardw — Thu, 02 Jan 2020 15:51:27 GMT

@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.)

Re: How to set value of 0 after transposing

Ksharp — Fri, 03 Jan 2020 13:13:25 GMT

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