BookmarkSubscribeRSS Feed
amyk
Fluorite | Level 6

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

9 REPLIES 9
ed_sas_member
Meteorite | Level 14

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
Fluorite | Level 6

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
Lapis Lazuli | Level 10

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
Super User Tom
Super User

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
Fluorite | Level 6

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
Lapis Lazuli | Level 10
Just use the _numeric_ and _character_ keywords like I did in my example. That will create an array with all numeric/character variables
unison
Lapis Lazuli | Level 10

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

-unison
ballardw
Super User

@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
Super User
proc stdize data=have out=want missing=0 reponly;
var _numeric_;
run;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1803 views
  • 0 likes
  • 7 in conversation