topic PROC SQL to produce distribution on all DATE variables in a dataset in SAS Programming

PROC SQL to produce distribution on all DATE variables in a dataset

JamieTee — Mon, 18 May 2020 23:37:30 GMT

I have a few datasets for which I have many various DATE variables. I'm looking to get the data distribution on each one. I've seen past posts which describe how to get min, max, nmissing, but I cannot seem to find a way to get the Q1 and Q3 marks (and if possible P1 and P99).

I need to use PROC SQL because I am accessing data through some data servers which operate through PROC SQL.

Thank you kindly!

Re: PROC SQL to produce distribution on all DATE variables in a dataset

Reeza — Tue, 19 May 2020 01:26:39 GMT

If you're going through SQL and must use SQL are you using Explicit Pass through? Otherwise you can use the standard PROC MEANS approach.

If you are using explicit pass through, then your code needs to be SQL specific and you need to use the specific functions that align with your SQL variant.

How do you know which variables are dates that you want to analyze? Based on variable type or a naming convention?

@JamieTee wrote:

I have a few datasets for which I have many various DATE variables. I'm looking to get the data distribution on each one. I've seen past posts which describe how to get min, max, nmissing, but I cannot seem to find a way to get the Q1 and Q3 marks (and if possible P1 and P99).

I need to use PROC SQL because I am accessing data through some data servers which operate through PROC SQL.

Thank you kindly!

Re: PROC SQL to produce distribution on all DATE variables in a dataset

JamieTee — Tue, 19 May 2020 01:34:56 GMT

Hi @Reeza thank you for your reply!

I looked up explicit pass through (https://communities.sas.com/t5/SAS-Programming/Implicit-vs-Explicit-SQL-Pass-through-SQL-Query-in-SAS/td-p/261905), and it looks like that's exactly what I'm using.

I would be identifying the DATE variables based on their format, which is generally DATE9. - would it be possible to search by the 'Type' of the variable instead (in case something is in a different DATE format)? Thank you kindly for your help.

Re: PROC SQL to produce distribution on all DATE variables in a dataset

Reeza — Tue, 19 May 2020 01:39:49 GMT

How I would do this is first to identify all my date variables.

All DB have dictionary tables that indicate the variable name and type. You can query that to build your list of variables you need to apply it to.

Second is to figure out what the function is you need for explicit pass through based on the SQL variant. Most have it, but they're never efficient at those calculations by the way.

If your data is in the smaller range (few million) I'd recommend downloading the data and then using a full SAS approach. That'll save you programming time but it'll take slightly longer to run - I suspect you won't make up the run time difference in this process but you'll learn how to do it I suppose.

Re: PROC SQL to produce distribution on all DATE variables in a dataset

PGStats — Tue, 19 May 2020 02:32:58 GMT

If the external DBMS doesn't provide the tools to calculate the quantiles, it could surely give you the record counts for each date. You could thus query for the dates and their frequencies and use proc univariate with a freq statement to get the quantiles. I think you would have to do this separately for each date variable.

hth