Stack Overflow
Stack Overflow

Questions tagged [proc-sql]

proc sql is a SAS procedure used to submit SQL statements to the SAS compiler. For Oracle Pro*C, please use [oracle-pro-c].

proc sql; is a SAS procedure used to submit SQL statements to the SAS compiler. It is optionally terminated by a quit; statement. An example of typical syntax would be:

proc sql;
create table z as
  select * 
  from x
  left join y
  on x.id=y.id
  order by 1,2,3;
quit;

A more 'SAS specific' example would be the direct creation of macro variables, such as:

proc sql noprint; 
select someVariable into: MyMacroArray separated by ' ' 
  from work.Input where varCondition='True';

For further details on syntax, click here (v9.4) or here (v9.3)

794 questions
2
votes
4 answers

Extract left part of the string in SAS?

Is there a function SAS proc SQL which i can use to extract left part of the string.it is something similar to LEFT function sql server. in SQL I have left(11111111, 4) * 9 = 9999, I would like to something similar in SAS proc SQL. Any help will be…
staq
  • 163
  • 3
  • 3
  • 11
2
votes
2 answers

How to convert date in SAS to YYYYMMDD number format

In test_1 table, the my_date field is a "DATE9." format. I would like to convert it to a pure numeric format (number length 8) which is of the form YYYYMMDD. I would also like to do this in a proc sql statement ideally. Here's what I have so…
cammil
  • 9,499
  • 15
  • 55
  • 89
2
votes
3 answers

return the most frequent value within each group

Trying to create a table that displays the most frequent ndc# for a given patient(pat_seqno). My having clause have not worked thus far and this is my most recent attempt, which returns an error that ndc_count is not found in the contributing…
user2448666
  • 329
  • 1
  • 6
  • 14
2
votes
2 answers

create unique id variable based on existing id variable

Trying to make a more simple unique identifier from already existing identifier. Starting with just and ID column I want to make a new, more simple, id column so the final data looks like what follows. There are 1million + id's, so it isnt an…
user2448666
  • 329
  • 1
  • 6
  • 14
2
votes
3 answers

minimum value for each unique id

This should be fairly simple and thanks in advance. I have a unique ID column and a start column populated with integers. I want to create a new column populated with the minimum start date for each unique ID. example follows: ID START 1 23 1 …
user2448666
  • 329
  • 1
  • 6
  • 14
2
votes
1 answer

SAS Proc SQL get records with nearest date to a specific date

I have 2 tables as below: Table 1, a user listing table: Year Month Id Type 2010 3 1 A 2010 5 2 B 2010 10 1 A 2010 12 1 A Table 2 describes user promotion history: Promote Date Id 2/20/2010 1 5/20/2010 1 (4/2010…
Luke Henz
  • 137
  • 3
  • 5
  • 12
2
votes
2 answers

Why didn't SAS use my index?

I have a large SAS dataset sorted by field 'A'. I'd like to do a query that references fields 'A' and 'B'. To speed up performance I created an index on 'B'. This results in an unhelpful message: INFO: Index B not used. Sorting into index order…
Two Bit Gangster
  • 973
  • 2
  • 9
  • 14
2
votes
2 answers

SAS Proc SQL Database Table Insert

Using SAS's Proc SQL, is there a way to insert records from a SAS Dataset into a table in the open SQL Server connection? Something like this (which doesn't work): proc sql exec; connect to sqlservr as DataSrc (server=my-db-srvr…
CuppM
  • 1,668
  • 4
  • 19
  • 30
1
vote
2 answers

catx function where columns are null

I am working with catx function in sas enterprise guide. When I want to concatenate 2 columns I am using catx('-',column1,column2) It works fine but when both column1 and column2 are null it still gives '-' as output How to restrict this, if both…
sra
  • 167
  • 8
1
vote
4 answers

How do I loop through table records in SAS?

I am new to SAS and trying to solve the following scenario: Scenario: A loan (123) has more than 2 transactions on a given transaction date. And, each record has a columns: ID, Trans_Amt, LoanNo, etc. I was asked to create a column to show if two…
Dileep
  • 11
  • 2
1
vote
3 answers

How to group records so the groups contains only specific variables? (proc sql)

I have a database of people with information that in which households they are included. How can I group in one statement households in which lives only woman or only a specific group of people? Example: So from this example I would like to get…
Kristián Óvári
  • 33
  • 4
1
vote
1 answer

Find top N per category using Proc SQL

I'm trying to select the top 1 record belonging to each group in proc sql, but not been able to identify a correct way to do it in proc sql. I'm trying to find top account tied to the store with transactions and total_sales ordered in descending…
hk2
  • 487
  • 3
  • 15
1
vote
3 answers

Remove records based on conditions in SAS

I am completely new to SAS and I need to do a simple task. say I have a table like: I need to remove all instances of sampCode for which at least one SampCode reflect the condition: 2*res > max. that is, not just removing the row, but the entire…
efz
  • 425
  • 4
  • 9
1
vote
4 answers

How to run a different query if table is empty one month earlier

How to run a different query if the output table is empty. My current query is: PROC SQL; CREATE TABLE WORK.QUERY_FOR_A_KUNDESCORINGRATINGRE AS SELECT t1.PD, t1.DATO, t1.KSRUID FROM DLKAR.A_KUNDESCORINGRATINGRETRO…
Christoffer
  • 326
  • 1
  • 4
  • 20
1
vote
2 answers

How to create new column in PROC SQL with sum of values per month in SAS enterprise Guide?

I have query in proc sql in SAS Enterprise Guide like below: proc sql; select event , mnth , value from table1 order by mnth, name ;quit; Above proc sql query gives result as below: event| mnth | value -----|-------|------- ABC | APRIL |…
dingaro
  • 2,156
  • 9
  • 29
1 2 3
…
52 53