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
0
votes
3 answers

How to find a match between two data sets and extract values in SAS

I want to compare two datasets and extract the matching observations to a new data set. For example, I have two datasets, one from October and another from November, as follows. Dataset October visitor_id ctn visits kjsjakd83 3243244234 1 …
anurag choubey
  • 23
  • 1
  • 3
0
votes
1 answer

SAS (University Edition), Proc SQL error involving where statement in create table

So, I've listed below the code that I was inputting and the error that I was receiving. I've done this below on the normal SAS on the computers at school, but now I'm using a laptop and using SAS University Edition. I ran my data through PROC…
Fmonkey2001
  • 147
  • 7
0
votes
2 answers

Statement not executed due to NOEXEC option

Even in proc sql I'm not using noexex then again why getting error. same sql query in executed on hive and it give correct result no syntax error. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 58 …
Ashutosh SIngh
  • 931
  • 3
  • 13
  • 26
0
votes
1 answer

including a max statement inner join with proc sql

I'm trying to get all of all of a series of variables while pulling off of the most recent possible update date (PD_LAST_UPDATE) some fields were updated yesterday, some fields might have been a year ago, so I can't just do PD_LAST_UPDATE =…
Kumachorou
  • 1
  • 1
0
votes
1 answer

Using WHERE in PROC SQL Error

Currently I'm trying to filter the output I have to display only products which have discounts greater than or equal to 60%. The variable discount lists all their values in the format such as 50%, 60% etc. PROC SQL; SELECT…
Dangerous Game
  • 103
  • 1
  • 1
  • 6
0
votes
1 answer

Inner join of two table on three variable

I want to do an inner join between a table a and another table b which contain 2 columns, and I want the key_service from table a equal to the key service element of variable key_service from table b and also equal to the element of variable…
Mostafa90
  • 1,674
  • 1
  • 21
  • 39
0
votes
2 answers

SAS: using group by in proc sql doesn't separate out instances chronologically

Consider the following SAS code: data test; format dt date9. ctry_cd $2. sn $2.; input ctry_cd sn dt; datalines; US 1 20000 US 1 20001 US 1 20002 CA 1 20003 CA 1 20004 US 1 20005 US 1…
dustin
  • 4,309
  • 12
  • 57
  • 79
0
votes
1 answer

inline sql query error

I am writing this basic sql statement in proc sql yet SAS throws me an error. proc sql; select interest from (select * from project.data_model order by ethnicity desc, satscore desc); quit; ERROR 79-322: Expecting a ). ERROR 22-322: Syntax…
newbie49
  • 1
  • 2
0
votes
2 answers

select rows in a union based on a third table with proc

I want to union the same table this way ID B C D E 1 11 12 13 14 2 21 22 23 24 3 31 32 33 34 to ID B C D E 1 11 12 13 14 2 21 22 23 24 3 31 32 33 34 11 13 14 12 21 23 24 22 31 33 34 32 based on the IDs of a third table. (I want to have only…
user3658367
  • 641
  • 1
  • 14
  • 30
0
votes
1 answer

SAS date operation within proc sql

I'm very new to SAS, and am trying to modify this piece of code proc sql; select a, Current_Date - 2 - b as some_date from table Current_Date is a function in sas. I'm trying to replace it with my own date. a and b are column names in a database…
user2773013
  • 3,102
  • 8
  • 38
  • 58
0
votes
3 answers

How to select within the closest thing to Y in SAS as a starting point

So I have a dataset where I want to select the closest records to point X for my output, What I have is PROC SQL ; create table Check_vs_Excel2 as SELECT PROPERTY, START_DATE, END_DATE, DAY_OF_WEEK, MARKET_CODE_PREFIX, RATE_PGM, ROOM_POOL,…
Kumachorou
  • 37
  • 3
0
votes
1 answer

How do I limit the number of rows in a proc sql table to ones which contain the highest date

I am trying to perform a large query on my mainframe, what I need to do is pull down all of the rows which have the highest PD_START_DATE. I figure it's something like PROC SQL ; SELECT PD_PROP_NUM, PD_RATE_PGM, PD_START_DATE, PD_END_DATE,…
Kumachorou
  • 37
  • 3
0
votes
1 answer

Truncation with COALESCE function in PROC_SQL

I am attempting to coalesce two data items in PROC SQL and am getting truncation. When I try the same process in a dataset I am also getting truncation, but in a different manner. Consider the following: data test; var_a = '111111'; var_b =…
gdogg371
  • 3,879
  • 14
  • 63
  • 107
0
votes
1 answer

PROC SQL Incorrectly Eliminating Missing Values in pull from Oracle data base

All my missing values are being deleted when I do not wish them to be. I am assuming SAS and Oracle are not playing nice - but I do not know the solution. Thank-you if you can offer any ideas about this error. Observe the character field SEX has…
Wes McClintick
  • 497
  • 5
  • 14
0
votes
2 answers

SAS - Comparing observations within a group to pick values

I have 4 columns in my SAS dataset as shown in first image below. I need to compare the dates of consecutive rows by ID. For each ID, if Date2 occurs before the next row's Date1 for the same ID, then keep the Bill amount. If Date2 occurs after the…
Keneni
  • 705
  • 5
  • 12
1 2 3
…
52 53