turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- iterative computation by groups

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-14-2014 02:34 AM

Hi

I have difficulty writing a code in proc sql to do the following iterative computation.

The data with three groups looks like this (first ten observations),

Group Var

2 .23

2 .37

2 .10

2 .20

2 .10

and within each group numbers add up to one.

I would like to get the sum the squared difference of all numbers.

For example for the first group in bold, I would like to do something like thisUM[ (.60-.20)**2 + (.60-.20)**2 + (.20-.20)**2],

for the second there will be like sum of ten squared differences. Finally I would like divide this number by the number of observations in each group.

How can I do this with proc sql

Thank you for your help,

Metin

Accepted Solutions

Solution

06-14-2014
09:29 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-14-2014 09:29 AM

Just take all possible combinations (FULL JOIN) of the data with itself. This will give you the values you listed, but also the same with the numbers in the opposite order. It will also include where you have crossed value with itself, but that will contribute 0 to the total sum of squares. So just divide the sum by 2 to get back to the number you requested.

To find the number in each group notice that if N is 3 then there are 3*3 rows when crossed with itself.

**proc** **sql** noprint ;

create table want as

select a.grp

, sum( (a.val - b.val) ** **2** )/**2** as sumsq

, (calculated sumsq)/sqrt(count(*)) as meansq

from have a

, have b

where a.grp = b.grp

group by **1**

order by **1**

;

**quit**;

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-14-2014 09:24 AM

I am afraid it is hard for SQL but easy for data step. Are you trying to get somewhat model's average square error ?

data have; input Group Var ; cards; 1 .60 1 .20 1 .20 2 .23 2 .37 2 .10 2 .20 2 .10 3 .57 3 .43 ; run; data want; set have; by group; array x{100} _temporary_; if first.group then do;n=0; sum=0;call missing(of x{*}) ;end; n+1; x{n}=var; if last.group then do; do i=1 to n-1; do j=i+1 to n; sum+(x{i}-x{j})**2; end; end; chisq=divide(sum,n); output; end; drop i j n var; run;

Xia Keshan

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-14-2014 04:05 PM

Thank you Xia and Tom for your time and codes.

Your answers have been very helpful, especially because I had a chance to compare both code (proc sql and data step) and understand the logic.

Solution

06-14-2014
09:29 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-14-2014 09:29 AM

Just take all possible combinations (FULL JOIN) of the data with itself. This will give you the values you listed, but also the same with the numbers in the opposite order. It will also include where you have crossed value with itself, but that will contribute 0 to the total sum of squares. So just divide the sum by 2 to get back to the number you requested.

To find the number in each group notice that if N is 3 then there are 3*3 rows when crossed with itself.

**proc** **sql** noprint ;

create table want as

select a.grp

, sum( (a.val - b.val) ** **2** )/**2** as sumsq

, (calculated sumsq)/sqrt(count(*)) as meansq

from have a

, have b

where a.grp = b.grp

group by **1**

order by **1**

;

**quit**;