topic Re: Creating a table using SQL in SAS Programming

Creating a table using SQL

fama — Sun, 27 Jan 2019 02:14:36 GMT

Hi all,

I have a data set like this:

V1 V2 v3

1 1 2

1 2 1

1 2 5

1 1 2

1 2 2

2 2 3

2 1 6

3 2 1

3 4 3

What I want is:

For each v1 in each v2 : I want to find distinct v3 and then count them ( say we have 4 distinct value in V3 and each of them has different frequency) Then square each frequency and then sum up them

Was wondering if the below code is right.

Proc sql :

create table X as select *, sum ( count ( distinct v3))*2 ) as want

group by V1 V2;

run;

Thank you for your help

Re: Creating a table using SQL

Reeza — Sun, 27 Jan 2019 02:34:08 GMT

Did you try running it? What happened?

But to answer your question, the code is not correct.

@fama wrote:

Hi all,

I have a data set like this:

V1 V2 v3

1 1 2

1 2 1

1 2 5

1 1 2

1 2 2

2 2 3

2 1 6

2 1 6

3 2 1

3 4 3

3 4 3

.

What I want is:

For each v1 in each v2 : I want to find distinct v3 and then count them ( say we have 4 distinct value in V3 and each of them has different frequency) Then square each frequency and then sum up them

Was wondering if the below code is right.

Proc sql :

create table X as select *, sum ( count ( distinct v3))*2 ) as want

group by V1 V2;

run;

Thank you for your help

Re: Creating a table using SQL

fama — Sun, 27 Jan 2019 02:39:41 GMT

I did not try. The data is quite big and it takes time to be ran.
I wanted to make sure if it is correct.
What is the correct one then?
Thanks

Re: Creating a table using SQL

Reeza — Sun, 27 Jan 2019 02:42:23 GMT

If the data is too big, try playing around with a subset instead.

Here's the code to get a subset if you need that:

data have;
input V1  V2  v3;
cards;
1      1    2
1      2    1
1      2    5
1      1    2
1      2    2
2     2     3
2     1    6
2     1    6
3     2    1
3     4    3
3     4    3
;;;;
run;

or:

data temp;
set have (obs=100);
run;

Post exactly you'd expect to see if your actual data was what you posted initially (my have data set) and I'll see what I can help you with.

@fama wrote:
I did not try. The data is quite big and it takes time to be ran.
I wanted to make sure if it is correct.
What is the correct one then?
Thanks

Re: Creating a table using SQL

fama — Sun, 27 Jan 2019 02:44:59 GMT

The data is 170 GB!
Even finding the subset takes hours..
I will try thanks..
But what is wrong with the code?

Re: Creating a table using SQL

novinosrin — Sun, 27 Jan 2019 03:01:40 GMT

run your code using your sample and look at the output and see If it matches your expected result,

If you want us to provide you a solution, plz post the expected output for a prospective input

Re: Creating a table using SQL

fama — Sun, 27 Jan 2019 03:17:11 GMT

Thanks for your reply:
Here is the data and expected output.

data have;
input V1 V2 v3 want;
cards;
1 1 1 10
1 1 1 10
1 1 1 10
1 1 2 10
1 2 6 14
1 2 3 14
1 2 3 14
1 2 4 14
1 2 4 14
1 2 4 14

;;;;
run;
10=( 3*2)+ (1*2) where 3 is the frequency of 1 in V3 and 1 is the frequency of 2 in V3
14= (1*2) +(2*2)+(3*2) where 1 is the frequency of 6 in V3 and 2 is the frequency of 3 in V3 and 3 is the frequency of 4

Re: Creating a table using SQL

novinosrin — Sun, 27 Jan 2019 03:27:32 GMT

Ok, how did you get the 10 and 14. What am i missing?

@fama wrote:

10=( 3*2)+ (1*2) where 3 is the frequency of 1 in V3 and 1 is the frequency of 2 in V3
14= (1*2) +(2*2)+(3*2) where 1 is the frequency of 6 in V3 and 2 is the frequency of 3 in V3 and 3 is the frequency of 4

Re: Creating a table using SQL

fama — Sun, 27 Jan 2019 03:35:45 GMT

we need to first look at V2. for each distinct value say 1 we count the distinct values of V3.
When V2 =1 , V3 has 1 (frequency =3) and 2(frequency =1).
then we need to obtain the square of each frequency that means 3*2 and 1*2 and sum up the results that means 9+1= 10
Hope it is clear
Thanks

Re: Creating a table using SQL

novinosrin — Sun, 27 Jan 2019 03:59:18 GMT

@fama Thank you & yes clear. One last question, do you really want this in proc sql?

Re: Creating a table using SQL

fama — Sun, 27 Jan 2019 04:00:37 GMT

Sorry to get Square I should have used **.

Yes I want to create a table using SQL in SAS

Re: Creating a table using SQL

novinosrin — Sun, 27 Jan 2019 04:26:25 GMT

data have;
input V1 V2 v3;
cards;
1 1 1 10
1 1 1 10
1 1 1 10
1 1 2 10
1 2 6 14
1 2 3 14
1 2 3 14
1 2 4 14
1 2 4 14
1 2 4 14
;;;;
run;

proc sql;
create table fama_want as
select a.*,want
from have a, (select v1,v2, sum(c) as want from (select *, count(v3)**2 as c from have group by v1, v2,v3 )
group by v1,v2) b
where a.v1=b.v1 and a.v2=b.v2;
quit;

I might be away for supper and stuff. Let us know if you need some clarification

Re: Creating a table using SQL

fama — Sun, 27 Jan 2019 05:25:11 GMT

Thank you very much Novinosrin.
I really appreciate your help.
The code works well.
Could you please clarify it for me. really like how it woks and learn...
Thanks again

Re: Creating a table using SQL

novinosrin — Sun, 27 Jan 2019 05:33:53 GMT

I'm glad. It's almost midnight here. I'll leave some notes in the morning. Please don't mind. I promise i'll soon as I wake up

Re: Creating a table using SQL

PGStats — Sun, 27 Jan 2019 05:52:01 GMT

I would do:

data have;
input V1 V2 v3;
cards;
1 1 1 
1 1 1 
1 1 1 
1 1 2 
1 2 6 
1 2 3 
1 2 3 
1 2 4 
1 2 4 
1 2 4 
;

proc sql;
select a.*, sumSqFreq
from
    have as a inner join
    ( select v1, v2, uss(freq) as sumSqFreq
      from 
        ( select v1, v2, v3, count(*) as freq
          from have
          group by v1, v2, v3 ) 
      group by v1, v2 ) as b 
    on a.v1=b.v1 and a.v2=b.v2;
quit;

Are you sure you need to remerge the statistics with the original data?

Re: Creating a table using SQL

fama — Sun, 27 Jan 2019 08:31:03 GMT

Thank you PGStats for your reply and help.
Yes, I need the table for the next analysis.

Re: Creating a table using SQL

fama — Sun, 27 Jan 2019 08:31:21 GMT

I really appreciate it!

Re: Creating a table using SQL

novinosrin — Sun, 27 Jan 2019 22:17:46 GMT

The logic works by

1. steps of remerges to the original

2. inner most inline view or easy to understand as sub query computes the frequency**2 and the next sub qery computes the sum of squared frequency.

3. The computed sum of squares is then merged back with the original

4. You deterine the above by starting from processing in hierarchical steps fromthe inner most sub query to the outer most for each level of computation.

5. Best way to understand break each select clause and execute separately and view the results. You'll eventually get the hang of it.

IMHO proc sql can take a bit longer to grasp as it's not intutive as a datastep for the reason datastep's sequential processing gives greater control. But sql is fun nonetheless

Re: Creating a table using SQL

Reeza — Sun, 27 Jan 2019 22:48:56 GMT

@fama just one clarification no one has mentioned, * is multiplication ** is power.

3*2=6

3**2=9

Re: Creating a table using SQL

fama — Mon, 28 Jan 2019 07:57:34 GMT

Thank you
Maybe because I corrected this in one of my posts..