DATA Step, Macro, Functions and more

Macro Loop for 2000 Columns

Accepted Solution Solved
Reply
Super Contributor
Posts: 381
Accepted Solution

Macro Loop for 2000 Columns

[ Edited ]

I take a data with 2000 columns. Names of the columns are like q1,q2,q3.... I want to create a new table with the ones that have the  count of non missing(+zero) rows more than 70% in the column. I also want to join the some column whose percentage is less than 70 to the table. My question is , how do i get 2000 output for 2000 columns with a single program and how do i specify and add the column to be created to the data.

The table that I want to create should look like the one below.

 

Q1 resultPercantage

Q2 resultPercantage

Q3 resultPercantage

There are also some codes that I tried to create below.

%LET VARNAME =

Column1/*Q1*/

Column2/*Q2*/

Column3/*Q3*/

;

%percentage(0.70)

%macro procfreq(Var);

%macro percentage(deger);

PROC SQL;

Create Table new as

SELECT (tt.nonMissingCount/tt.toplamCount) as result format=percent10.2,

case

         when calculated result>&deger then out=yeni /*????*/

         else 'false /*????*/'

         end as yeniSonuc

from(

SELECT DISTINCT

(SELECT COUNT(&Var) as NonMissing from work.exampledata WHERE &Var ne 0) as nonMissingCount

,(SELECT COUNT(&Var) as SumCnt from work.exampedata) as sumCount from work.exampledata)as tt;

QUIT;

%mend;

%mend;

%macro loop;

%let i=1;

%do %while(%scan(&VARNAME.,&i.,%STR( ))~=);

%procfreq(%scan(&VARNAME.,&i.,%STR( )));

%let i=&i.+1;

%end;

%mend;

%loop;

 

I will be very greatful if I hear from you as soon as possible.


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
SAS Employee
Posts: 12

Re: Macro Loop for 2000 Columns

[ Edited ]

This code will create a table with one row for each column and the %age of records with a >0 value;  any splitting of records with >70% etc can then easily be accomplished.

 

 

%let colCnt=2000;
data out_data(keep=var pcntage);
	format pcntage percent7.2;
    array t t1-t&colCnt.;
	retain t1-t&colCnt. 0;  * retain some total counts;
	set x end = eof;
		array q q1-q&colCnt.;
		do i=1 to &colcnt;
		    t(i)=t(i)+(q(i)>0);  * add each non-missing to the total count ;
		end;
		if eof then do i=1 to &colcnt.;
			var='q'||strip(put(i,best.));  * create column name ;
			pcntage=t(i)/_n_; * calculate %age ;
			output; * output one row for each of the analyzed columns;
		end;
	run;

 

 

View solution in original post


All Replies
Super User
Posts: 17,912

Re: Macro Loop for 2000 Columns

I doubt this is efficient but here's my macro:
https://gist.github.com/statgeek/c3a9ddcb002c469e9d61

Another way might be to transpose your data so you have a column identifying the variable and then the value and run a single proc sql or proc freq.
Super Contributor
Posts: 381

Re: Macro Loop for 2000 Columns

Where this count value comes from ?

 

Number_Missing=&obs_count-count;

Super User
Posts: 17,912

Re: Macro Loop for 2000 Columns

from the proc freq output, the macro generates it.

At the bottom of the code is an example of how to execute the macro, but I'll admit the documentation is non existent.
Respected Advisor
Posts: 3,777

Re: Macro Loop for 2000 Columns

Show example data. What you have followed by what you want to see.
Super Contributor
Posts: 381

Re: Macro Loop for 2000 Columns

 

There are 4000 numbers of Q columns

 

sample.png

 

I want to see all of them percentage like below then i need to transpose it

 

Q1.png

 

 

Q1 87.50%;

Q2 50.50%;

Q3 40.50%;

Q4 90.50%;

Q5 65.50%;

Q6 70.50%;

Q7 60.50%;

Q8 80.50%;

Super User
Posts: 17,912

Re: Macro Loop for 2000 Columns

It looks like you're in EG?

 

Use Tasks>Transpose and switch your data structure such that it becomes

 

ID Question Value

1 Q1 1

1 Q2 0

1 Q3 1

....

 

 

1 Q5000 0

2 Q1 0

 

 

Then use Query Builder with an N() and NMISS() function to determine the number of missing with the Question in the GROUP By Column.

Super Contributor
Posts: 381

Re: Macro Loop for 2000 Columns

Thanks Reeza, i'll check. On the other hand your code involves a lot of things. I just a little bit confused( https://gist.github.com/statgeek/c3a9ddcb002c469e9d61  ) Can you show me a simpler method to reach my loop and nonmissing aim,please .

 

Thank  you.

Super User
Posts: 17,912

Re: Macro Loop for 2000 Columns

Here's a worked example using both a proc transpose and the macro. The macro is inefficient but works quickly and across all types of variables, numeric/character. FYI You should show data in the text, rather than images, so we can copy and paste it into our code, it helps us to help you.  

 

*Generate sample data;
data have;
array q(20) q1-q20;
do id=1 to 100;
do i=1 to 20;
q(i)=floor(rand('uniform')*100+1);
x=rand('bernoulli', 0.1);
if x=1 then q(i)=.;
end;
output;
end;

drop i x;
run;

*check format;
proc print data=have(obs=5);
run;

*Flip dataset;
proc transpose data=have out=flipped(rename=(_name_=Question Col1=Value));
by id;
run;

*check format again;
proc print data=flipped(obs=10);
run;

*Count number missing using SQL step;
proc sql;
create table want as
select question, nmiss(value) as NMISS, count(question) as N,
	nmiss(value)/count(question) as PCT_MISS format=percent8.1
from flipped
group by question;
quit;

proc print data=want;
run;

*Sample output if used macro, make sure to run the macro first;
%sum_missing(work, have, want_macro);
proc print data=want_macro;
run;
Super User
Super User
Posts: 6,502

Re: Macro Loop for 2000 Columns

You do not need macro code, much less a maco loop.

Look at this question that just used PROC FREQ to generate the counts for ALL variables.

https://communities.sas.com/t5/Base-SAS-Programming/Missing-Values-for-character-variables/m-p/20924...

 

Solution
‎09-25-2015 06:23 AM
SAS Employee
Posts: 12

Re: Macro Loop for 2000 Columns

[ Edited ]

This code will create a table with one row for each column and the %age of records with a >0 value;  any splitting of records with >70% etc can then easily be accomplished.

 

 

%let colCnt=2000;
data out_data(keep=var pcntage);
	format pcntage percent7.2;
    array t t1-t&colCnt.;
	retain t1-t&colCnt. 0;  * retain some total counts;
	set x end = eof;
		array q q1-q&colCnt.;
		do i=1 to &colcnt;
		    t(i)=t(i)+(q(i)>0);  * add each non-missing to the total count ;
		end;
		if eof then do i=1 to &colcnt.;
			var='q'||strip(put(i,best.));  * create column name ;
			pcntage=t(i)/_n_; * calculate %age ;
			output; * output one row for each of the analyzed columns;
		end;
	run;

 

 

Super Contributor
Posts: 381

Re: Macro Loop for 2000 Columns

Thanks a lot. I was very important for me thanks again Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 344 views
  • 0 likes
  • 5 in conversation