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

SELECT DISTINCT ON in SAS PROC SQL

How can I select ALL ROWS and where A ROW is existent more then once, (based on ID) just take one (doesn't matter which one). So I have: ID name val =========== 1 | aa | 32 2 | aa | 32 3 | ad | 32 3 | zy | 11 4 | rr | 21 5 | oi | 44 5 | df |…
G-M
  • 296
  • 1
  • 6
  • 15
0
votes
1 answer

Conditionally delete the most recently inserted observation in SAS

I have two tables A and B that look like below. Table A rowno flag1 flag2 flag3 1 1 0 0 2 0 1 1 3 0 0 0 4 0 1 1 5 0 0 1 6 0 …
dozel
  • 127
  • 1
  • 3
  • 9
0
votes
1 answer

PROC SQL INNER JOIN QUERY

i am learning sas proc sql statement. I observed that although the result are the same for following two methods, the real and cpu time is different. I wonder why there exist the difference. data data1; input name1 $ choice $; …
useR
  • 3,062
  • 10
  • 51
  • 66
0
votes
1 answer

SAS PROC SQL Generating a Cartesian Product

I am learning PROC SQL and wonder why following two result are different: I have two data set as following data data1; input name $ value; datalines; a 1 b 3 c 5 ; run; data data2; input name $ value; datalines; A 2 B 4 C 6 D…
useR
  • 3,062
  • 10
  • 51
  • 66
0
votes
0 answers

How to concatenate multiple rows values into one row in SAS Proc SQL

I have the data below. I want to keep unique make variable and concatenate all the information about the unique car maker into one row like this: !!I want use Proc SQL only to do all the job. Desire output: AMC Concord 22 2012-02-19; Pacer 17…
user3525837
  • 57
  • 3
  • 9
0
votes
1 answer

PROC SQL subquery/correlated query

pro sql number; create table daily_total as select distinct trans_dt, sum((select (quantity * unit_price) from transaction_detail where subcat_num = 1111 and trans_dt = a.trans_dt)) from transaction_detail as…
Jackson
  • 365
  • 2
  • 10
0
votes
2 answers

Assigning data from SAS data set to variable (SAS E Guide 5.1)

I use SAS to execute queries, and sometimes send an email to my team afterward to confirm that the query has executed. However, I never really know if it was successful unless I check manually. For example, I have a SAS job that executes a stored…
Joshua Schlichting
  • 3,110
  • 6
  • 28
  • 54
0
votes
1 answer

Using a SAS Macro Variable and link it to the Contain Operator in Proc SQL

I have a a table called Tabling. Name Number Price A 10 Price B 11 Quantity A 12 Quantity B 13 How do I write a Proc SQL piece of Code where I create a variable Saying %let…
cordelia
  • 133
  • 3
  • 12
0
votes
2 answers

Is it possible to merge two datasets where a variable's value in the first is used to select a variable in the second?

I would like to know how to merge two datasets in SAS using a variable's value in the first dataset to select and test a variable in the second dataset. As an example consider two datasets. The first dataset contains four baby names and the days…
Adam Black
  • 337
  • 3
  • 13
0
votes
1 answer

Using SUBSTR and CHARINDEX into PROC SQL (SAS)

I have written out a SQL query for Microsoft SQL Server, but now I am trying to convert it into SAS PROC SQL and I am running into issues. Data: ╔══════════╦══════════╦════════╗ ║ Name ║ Question ║ Answer ║ ╠══════════╬══════════╬════════╣ ║…
vanellope1
  • 43
  • 1
  • 1
  • 4
0
votes
4 answers

SQL CASE exist then value

I'm working on a SQL query like that : PROC SQL; CREATE TABLE WORK.test AS SELECT ARTICLES.sku, ARTICLES.family_set, ARTICLES.classe_article, CASE WHEN EXISTS (SELECT CATALOG_PAGE2 FROM ODS.ARTICLECOMPANY14…
Mitchum
  • 107
  • 2
  • 16
0
votes
1 answer

SAS 9.3 do loop within proc sql select (dynamic libname with dataset name)

i would like to join the dataset like the following by using do loop inside the proc sql: i have macro variables yymm0-yymm4; yymm0=1505; yymm11=1501. this is the code without do-loop: proc sql; create table output as select var1, var3 from…
useR
  • 3,062
  • 10
  • 51
  • 66
0
votes
1 answer

SAS 9.3 do loop within proc sql select

i would like to join the dataset like the following by using do loop inside the proc sql: i have macro variables yymm0-yymm4; yymm0=1505; yymm11=1501. this is the code without do-loop: proc sql; create table output as select var1, var3 from data1…
useR
  • 3,062
  • 10
  • 51
  • 66
0
votes
2 answers

Basic SQL (compile in SAS) - retrieve top 10 entries by frequency

I'm new to SQL and trying to run this piece of code to do a count on the frequency of each entry, but I'm getting an error (and can't figure out why - no real debugging in this environment, just "ERROR 22-322: Syntax error, expecting one of the…
cappuccino
  • 3
  • 1
  • 2
0
votes
2 answers

SAS-Adding a Zero where condition not met

Any help on this would be greatly appreciated. I am trying to add a '0' value to my data based on whether a particular ID submitted a certain code or not. To better illustrate what I am trying to do here is a sample dulled down data set, with three…
Brad
  • 85
  • 12
1 2 3
…
52 53