1. Sas Proc Sql Count
2. Proc Sql Count Function
3. Sas Sql Update Multiple Columns
4. Update 2 Columns In Sql
5. Sas Proc Sql Count Into Statement

Is it possible to execute a multiple column update using proc sql? Example Query: update tableA T set ( column1, column2, column3) = (select S.column1, S.column2, S.column3 from tableB S where S.filter1 = T.Filter1) This type of update can be executed in Oracle Thanks for your help. Possible Duplicate: SAS proc SQL and arrays. I am trying to calculate the ratios of multiple columns, multiple times in SAS using SQL. For example, I want. Update column to the COUNT of rows for specific values in another column. Insert results of a stored procedure into a temporary table. Proc sql; select nbSamples, count(*) as nbPatients. The code I added previously gives the left column. The right count column for each # of samples is the problem. Message 4 of 6 (6,690 Views). Proc sql counting two variables. Mark as New; Bookmark; Subscribe; RSS Feed; Permalink. Solved: Hello, I am learning using PROC SQL to replace the data step for data extraction. I use PROC SQL / UPDATE statement, I found that I didn't. How to update values with multiple columns by using PROC SQL? Solved Reply. Topic Options. RSS Feed; Mark Topic as New; Mark Topic as Read. I'm having a problem updating multiple columns in a table. Usually when I submit an update that affects multiple columns it's from an application using a stored procedure similar to the following: ALTER PROCEDURE [dbo]. Proc-sql-view can be a one-level name, a two-level libref.view name, or a physical pathname that is enclosed in single quotation marks. Updating Tables through Views You can update one or more rows of a table through a view, with some restrictions.

Is there a better way of doing a query like this:

I need to count the number of distinct items from this table but the distinct is over two columns.

My query works fine but I was wondering if I can get the final result using just one query (without using a sub-query)

JeffNovitzkyNovitzky

Sas Proc Sql Count

18 Answers

If you are trying to improve performance, you could try creating a persisted computed column on either a hash or concatenated value of the two columns.

Once it is persisted, provided the column is deterministic and you are using 'sane' database settings, it can be indexed and / or statistics can be created on it.

I believe a distinct count of the computed column would be equivalent to your query.

Scott BeesonJasonHornerJasonHorner

Edit: Altered from the less-than-reliable checksum-only queryI've discovered a way to do this (in SQL Server 2005) that works pretty well for me and I can use as many columns as I need (by adding them to the CHECKSUM() function). The REVERSE() function turns the ints into varchars to make the distinct more reliable

JayTeeJayTee

What is it about your existing query that you don't like? If you are concerned that DISTINCT across two columns does not return just the unique permutations why not try it?

It certainly works as you might expect in Oracle.

edit

I went down a blind alley with analytics but the answer was depressingly obvious..

edit 2

Given the following data the concatenating solution provided above will miscount:

So we to include a separator..

Obviously the chosen separator must be a character, or set of characters, which can never appear in either column.

APCAPC

How about something like:

Probably just does the same as you are already though but it avoids the DISTINCT.

To run as a single query, concatenate the columns, then get the distinct count of instances of the concatenated string.

In MySQL you can do the same thing without the concatenation step as follows:

This feature is mentioned in the MySQL documentation:

Here's a shorter version without the subselect:

It works fine in MySQL, and I think that the optimizer has an easier time understanding this one.

Edit: Apparently I misread MSSQL and MySQL - sorry about that, but maybe it helps anyway.

starbeamrainbowlabsAlexander KjällAlexander Kjäll

Many (most?) SQL databases can work with tuples like values so you can just do:SELECT COUNT(DISTINCT (DocumentId, DocumentSessionId)) FROM DocumentOutputItems;If your database doesn't support this, it can be simulated as per @oncel-umut-turer's suggestion of CHECKSUM or other scalar function providing good uniqueness e.g.COUNT(DISTINCT CONCAT(DocumentId, ':', DocumentSessionId)).

A related use of tuples is performing IN queries such as:SELECT * FROM DocumentOutputItemsWHERE (DocumentId, DocumentSessionId) in (('a', '1'), ('b', '2'));

karmakazekarmakaze

There's nothing wrong with your query, but you could also do it this way:

if you had only one field to 'DISTINCT', you could use:

and that does return the same query plan as the original, as tested with SET SHOWPLAN_ALL ON. However you are using two fields so you could try something crazy like:

but you'll have issues if NULLs are involved. I'd just stick with the original query.

KM.KM.AlexanderIordanTanevIordanTanev

I found this when I Googled for my own issue, found that if you count DISTINCT objects, you get the correct number returned (I'm using MySQL)

I have used this approach and it has worked for me.

For my case, it provides correct result.

I wish MS SQL could also do something like COUNT(DISTINCT A, B). But it can't.

At first JayTee's answer seemed like a solution to me bu after some tests CHECKSUM() failed to create unique values. A quick example is, both CHECKSUM(31,467,519) and CHECKSUM(69,1120,823) gives the same answer which is 55.

Then I made some research and found that Microsoft does NOT recommend using CHECKSUM for change detection purposes. In some forums some suggested using

but this is also not conforting.

You can use HASHBYTES() function as suggested in TSQL CHECKSUM conundrum. However this also has a small chance of not returning unique results.

I would suggest using

How about this,

This will get us the count of all possible combinations of DocumentId, and DocumentSessionId

ADyson

Proc Sql Count Function

I had a similar question but the query I had was a sub-query with the comparison data in the main query. something like:

ignoring the complexities of this, I realized I couldn't get the value of a.code into the subquery with the double sub query described in the original question

So eventually I figured out I could cheat, and combine the columns:

This is what ended up working

You can just use the Count Function Twice.

In this case, it would be:

Nick

This code uses distinct on 2 parameters and provides count of number of rows specific to those distinct values row count. It worked for me in MySQL like a charm.

Cody Gray♦

protected by Community♦Mar 21 at 21:21

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged sqlsql-servertsql or ask your own question.

Sas Sql Update Multiple Columns

How can I get the number of rows affected by an UPDATE query in a Stored Procedure (SQL Server 2005), as a resultset. e.g.

Make a transformer name. X-men, Batman, Spiderman, Transformers, The naked. Wait you're a Transformer! You are a Transformer.a new transformer with a great personality. You could be on the Autobot team as Wildstrike or Saberquake, or as a Decepticon as Steelfrost or Electroshadow. Transformers name generator. This name generator will give you 10 random names for transformers. Transformers is a huge entertainment franchise dating back to 1984. It started out as a toy line of robotic beings who could be transformed into all sorts of vehicles, but it's today probably better known for the hugely successful film franchise.

Then return:

SamWMSamWM

3 Answers

AdaTheDevAdaTheDev

This is exactly what the OUTPUT clause in SQL Server 2005 onwards is excellent for.

EXAMPLE

Results in the following being returned

In your particular case, since you cannot use aggregate functions with OUTPUT, you need to capture the output of INSERTED.* in a table variable or temporary table and count the records. For example,

Russ CamRuss Cam

You might need to collect the stats as you go, but @@ROWCOUNT captures this:

Update 2 Columns In Sql

butterchicken

Sas Proc Sql Count Into Statement

butterchicken

Not the answer you're looking for? Browse other questions tagged sqlsql-server or ask your own question.