0

I'm new to databases and having an issue with sorting/merges. I currently work in base SAS. I connect to my data tables in PostgreSQL 13 using the following code:

libname name odbc dsn="PostgreSQL30" schema="schemaname";

I am having an issue merging tables between the two platforms, as SAS doesn't recognize the sort order from PostgreSQL, despite the fact that I know both are sorted "correctly" (by the same variables, in the same variable order). Are there optimal encoding and collation settings in PostgreSQL that are compatible with SAS? Our current settings are:

Encoding = 'UTF8'
LC_Collate = 'English_United States.1252'
LC_Type= 'English_United States.1252'

The encoding for the SAS table I'm working with is 'latin1 Western (Windows)' and my understanding is that SAS uses binary collation as a default. Thank you for any assistance.

Improve this question
5
  • 1
    Can you please explain how does the "SAS doesn't recognize the sort order" manifest itself? It's unclear what you expect and what is in fact happening. Commented Oct 7 at 18:14
  • Table rows don't have an order in relational database. You need to query with an ORDER BY clause. To get the binary sorting order in PostgreSQL, use ORDER BY col COLLATE "C". Commented Oct 7 at 18:39
  • I can explain further re:sort. I am trying to merge two tables using some basic SAS code 'data want; merge have (in=a) dbtable (in=b); on id year; if a; run;' I get an error from SAS that says that dbtable is not sorted properly for the merge, which is a requirement in SAS. I know, however that it is because it has been sorted in PostGres. I also did a trial where created a new table in SAS that is a copy of the dbtable and resorted it using this code: 'options sortpgm = sas; Proc sort data = dbtable out = dbtable2; by id year ; run; ' and that worked. Commented Oct 7 at 18:45
  • @LaurenzAlbe would "C" be an appropriate setting for the LC_Collate and LC_Type? Commented Oct 7 at 18:51
  • Please add information to the question, don't add it xas comment. Please add an explanation of how you sorted the table in PostgreSQL. Commented Oct 8 at 5:35

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.