Solved
Contributor
Posts: 60

Apply weight to SQL Freq table

I have the code below I found on a seperate forum (can't remember where) that takes 15 seperate variables and creates a single frequency table of the diagnoses and frequency. The original table is weighted and I would like to apply those weight to this table. Any ideas on how to do that?

Thanks

proc sql;
create table thesis.freqsvh as
SELECT Diagnoses
, count (*) as freq
FROM (
SELECT dx1 AS Diagnoses FROM thesis.vh
UNION ALL SELECT dx2 AS Diagnoses FROM thesis.vh
UNION ALL SELECT dx3 AS Diagnoses FROM thesis.vh
UNION ALL SELECT dx4 AS Diagnoses FROM thesis.vh
UNION ALL SELECT dx5 AS Diagnoses FROM thesis.vh
UNION ALL SELECT dx6 AS Diagnoses FROM thesis.vh
UNION ALL SELECT dx7 AS Diagnoses FROM thesis.vh
UNION ALL SELECT dx8 AS Diagnoses FROM thesis.vh
UNION ALL SELECT dx9 AS Diagnoses FROM thesis.vh
UNION ALL SELECT dx10 AS Diagnoses FROM thesis.vh
UNION ALL SELECT dx11 AS Diagnoses FROM thesis.vh
UNION ALL SELECT dx12 AS Diagnoses FROM thesis.vh
UNION ALL SELECT dx13 AS Diagnoses FROM thesis.vh
UNION ALL SELECT dx14 AS Diagnoses FROM thesis.vh
UNION ALL SELECT dx15 AS Diagnoses FROM thesis.vh
) AS myunion
Group by Diagnoses
ORDER by freq desc;
quit;

Accepted Solutions
Solution
‎01-03-2016 07:10 PM
Super User
Posts: 8,073

Re: Apply weight to SQL Freq table

Use SUM() aggregate function instead of COUNT().

``````SELECT Diagnoses
, sum(weight) as freq
FROM (
SELECT weight,dx1 AS Diagnoses FROM thesis.vh
UNION ....``````

All Replies
Super User
Posts: 23,683

Re: Apply weight to SQL Freq table

1. Transpose data so that each dx is on its own line, proc transpose
2. Proc surveyfreq with Weight statement

The code may actually be shorter.
Contributor
Posts: 60

Re: Apply weight to SQL Freq table

Can you please expand on that a little. I've never used proc survey before.

I created a wide dataset with the first row being DX1-15 and the weight variable. I have 1190 columns. Columns 3-1190 all contain the ICD-9 information.
Super User
Posts: 23,683

Re: Apply weight to SQL Freq table

Let's go back a step, what do you mean by "Apply the weights to this table"?

I assumed you wanted a count of diagnosis across all fields with the weight variable factored in. If you want something different, then my answer isn't correct.
Contributor
Posts: 60

Re: Apply weight to SQL Freq table

Tom's answer worked but thanks for the suggestions.
Solution
‎01-03-2016 07:10 PM
Super User
Posts: 8,073

Re: Apply weight to SQL Freq table

Use SUM() aggregate function instead of COUNT().

``````SELECT Diagnoses
, sum(weight) as freq
FROM (
SELECT weight,dx1 AS Diagnoses FROM thesis.vh
UNION ....``````

Contributor
Posts: 60

Re: Apply weight to SQL Freq table

Awesome that worked thanks.
🔒 This topic is solved and locked.