Taking count and max for each row based on the list

SAS_prep — Fri, 26 Apr 2019 15:51:28 GMT

Hi,

I have a Variable_ID and 8 quarters of data. This data is the output of a proc compare and I have created a varlist based on required quarters. i need the number of non missing and max for the varlist. I have attached the data and the desired output.

I have tried in two different ways but none of them giving me the correct results. It is considering all the columns.

Code1:

data want;

set date;

Count=N(Varlist);

Max=MAX(Varlist);

Run;

Code2:

Proc Sql;

Select count(variable_id) INTO: count from date; quit;

Data_null_;

call symput('count1',left(count));

run;

Proc Sql;

Select varlist INTO: list1 :list&count. from date; quit;

%macro dlt;

data want;

set date;

%do I = 1 to &count1.;

count=N(of &&list&I.);

max=Max(of &&list&I.);

%end;

%mend;

%dlt;

Am i missing something or is there any other way to achieve the desired output? please help

Re: Taking count and max for each row based on the list

smijoss1 — Sun, 28 Apr 2019 01:37:28 GMT

data test;

	varlist = '_2014Q1,_2014Q2,_2014Q3,_2014Q4';

	_2014Q1	=3;
	_2014Q2	=.;
	_2014Q3	=2;
	_2014Q4	=.;
	_2015Q1	=5;
	_2015Q2	=.;
	_2015Q3	=.;
	_2015Q4	=.;

	Array _tmp{*} _201: ; 

	call missing (Count_NotMiss, Max_Val);

	do _i = 1 to dim(_tmp);
		if _tmp(_i) ne . and index(varlist,vname(_tmp(_i))) > 0 then 
			do;
				Count_NotMiss + 1;
				Max_Val = Max(Max_Val,_tmp(_i));
			end; 
	end;

Run;

topic Taking count and max for each row based on the list in New SAS User

Taking count and max for each row based on the list

Re: Taking count and max for each row based on the list