BookmarkSubscribeRSS Feed
SAS_prep
Calcite | Level 5

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

1 REPLY 1
smijoss1
Quartz | Level 8
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;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 527 views
  • 0 likes
  • 2 in conversation