data step calculation

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

data step calculation

Hello to whomever can help

 

how to use data step to have the same result as of proc sql program as following:

 

proc sql;
	select states
		  ,sum(income)
	from data
	group by states;

proc sql;
	select count(states)
	from data
	group by states;

thank you!


Accepted Solutions
Solution
‎02-18-2016 12:53 AM
Respected Advisor
Posts: 4,606

Re: data step calculation

Your SQL queries do not create tables, they merely print results. So the equivalent data step must be a data _null_ step that writes to the print destination. Roughly :

 

proc sql;
	select states
		  ,sum(income)
	from data
	group by states;
quit;

proc sort data=data; by states; run;
data _null_;
sumIncome = 0;
do until(last.states);
	set data; by states;
	sumIncome + income;
	end;
file print;
put states income;
run;

proc sql;
	select count(states)
	from data
	group by states;
quit;

proc sort data=data; by states; run;
data _null_;
count = 0;
do until(last.states);
	set data; by states;
	count + 1;
	end;
file print;
put count;
run;
PG

View solution in original post


All Replies
Solution
‎02-18-2016 12:53 AM
Respected Advisor
Posts: 4,606

Re: data step calculation

Your SQL queries do not create tables, they merely print results. So the equivalent data step must be a data _null_ step that writes to the print destination. Roughly :

 

proc sql;
	select states
		  ,sum(income)
	from data
	group by states;
quit;

proc sort data=data; by states; run;
data _null_;
sumIncome = 0;
do until(last.states);
	set data; by states;
	sumIncome + income;
	end;
file print;
put states income;
run;

proc sql;
	select count(states)
	from data
	group by states;
quit;

proc sort data=data; by states; run;
data _null_;
count = 0;
do until(last.states);
	set data; by states;
	count + 1;
	end;
file print;
put count;
run;
PG
Grand Advisor
Posts: 17,308

Re: data step calculation

[ Edited ]

Don't use a data step use PROC MEANS instead. Using a demo dataset and assuming your grouping by sex, instead of state, here's an example. 

 

Proc means data=sashelp.class n sum;
Class sex;
Var weight;
Run;

You can do this in a data step if you really wanted but it's inefficient. 

Contributor
Posts: 31

Re: data step calculation

Thanks Reeza. I like you answer. Actually I knew I should use either PROC MEANS or PROC SQL, but they are PROCEDURES. There was a guy who asked me how to do it with DATA STEP. You are right DATA STEP is very inefficient on this.
Esteemed Advisor
Posts: 6,636

Re: data step calculation

Since both methods (PROC MEANS and DATA step) will require one pass through the dataset, in terms of CPU and I/O consumption they will be equally effective. Unless the data step requires a sort step to get the right order.

PROC MEANS is to prefer because you have less statements to write.

The data step method may be required if you need to calculate statistics that cannot be done with proc means/summary).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 386 views
  • 1 like
  • 4 in conversation