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
2 answers

SAS: merge/ join datasets by dynamic columns in the look up table

I want to join sas dataset with the look up table but the column/key for joining is a value in the look up table Dataset: table4 ID lev1 lev2 lev3 lev4 lev5 1 12548 14589 85652 45896 45889 2 …
Bharath
  • 95
  • 11
2
votes
5 answers

SAS PROC SQL: why there is a different when hard coding values within IN()?

I have a question about the following 2 codes in SAS PROC SQL. Code 1: (Standard Book version) CREATE TABLE WORK.OUTPUT AS SELECT "CLAIM" AS SOURCE, a.CLAIMID, a.DXCODE FROM DW.CLAIMS_BAV AS a WHERE a.SITEID = '0001' AND…
George
  • 4,514
  • 17
  • 54
  • 81
2
votes
1 answer

sql does not respect my fcmp function length

Can anybody explain to me how to get PROC SQL to give the results of my custom function the length I specify in the function definition? Datastep does it fine, but SQL gives me the default length of 200 characters. Here is code that demonstrates…
Roy Pardee
  • 196
  • 2
  • 12
2
votes
3 answers

Conditional join in SQL depending on a column value

I'm trying to do a join on a SAS table using PROC-SQL. Depending on the value of one variable in table A I need to join using a column, otherwise I need to use another column. proc sql; create table test as select A.*, A.PPNG_RVNE * B.perc_ress as…
Rods2292
  • 665
  • 2
  • 10
  • 28
2
votes
2 answers

Proc SQL assignment of missing values

If I run a query that produces zero rows, I still want a SAS dataset created with one row having all columns with missing values assigned. I found a way to do this using a separate data step: %let dsid = %sysfunc (open(myfile)); %let anyobs =…
99Valk
  • 203
  • 1
  • 7
  • 17
2
votes
1 answer

proc sql union with different variables

I'm trying to concatenate two tables using a proc sql - union where certain variables are unique to each table. Is there a way to do this without using a NULL placeholder variable? Basically the equivalent of the following data step. data total; set…
J_Lard
  • 1,083
  • 6
  • 18
2
votes
2 answers

SAS String comparison

I'm transitioning from SQL Server to SAS. In SQL server we could get away with string comparisons where 'abc ' = 'aBc' would be true. Is SAS so far I've had to STRIP and UPPER every string on every comparison. Is there an option that can be set to…
Ben
  • 485
  • 9
  • 19
2
votes
5 answers

Looping through tables and joining them in SAS

I can't figure out to solve one problem. I have unknown number of tables in one SAS library. I want to make a loop, which goes through them and "UNION ALL" those tables. Tables are pre-checked and they have the same structure. This tables include …
Noza
  • 35
  • 1
  • 7
2
votes
2 answers

Minute interval in mysql / sas or proc sql

Does any one know proc sql or data step have a function where I can convert datetime value into : 15 mins interval for example : 03NOV2010:00:00:02 --> 0-15 03NOV2010:00:16:02 --> 15-30
iamjeannie
  • 61
  • 2
  • 5
2
votes
1 answer

How can I count the number of active subscriptions per quarter in SAS?

I am working with subscription data for a magazine, and have compiled a dataset that includes the start and end dates of each customers' subscription. The dataset includes a unique ID, a First_Date column indicating the day their subscription…
Jamie Hall
  • 21
  • 2
2
votes
3 answers

SAS PROC SQL NOT CONTAINS multiple values in one statement

In PROC SQL, I need to select all rows where a column called "NAME" does not contain multiple values "abc", "cde" and "fbv" regardless of what comes before or after these values. So I did it like this: SELECT * FROM A WHERE NAME NOT CONTAINS "abc"…
puk789
  • 322
  • 2
  • 8
  • 28
2
votes
1 answer

Error when using Proc SQL and Case-statement

I'm trying to use Proc SQL and a case-statement to test for a condition and add an index. However, I cannot get the case-where statement to work even though the syntax seems to match the examples I've seen. Using the code below I get the following…
fileunderwater
  • 1,125
  • 2
  • 12
  • 31
2
votes
2 answers

sql updating values according to same variable in another table

I would like to update the values of “area” variable according to the new values of table B my try: update a set area=(select area from b) where a.id=b.id table A Id area 1 N 2 S 3 W 4 W 5 E table B Id area 1 W 2 …
progster
  • 877
  • 3
  • 15
  • 27
2
votes
2 answers

I've got the following two SQL tables (in MySQL):

I've got the following two SQL tables (in proc SQL): A +----+------+--------------+ | id | age | +----+------+ | 1 | 10 | +----+------+ | 2 | 20 | +----+------+ | 3 | 30 | +----+------+ | 4 | 40 | +----+------+ B +----+ | id…
Priyanka
  • 21
  • 1
2
votes
3 answers

Count consecutive consonants in e-mail address in SAS SQL

I would like to identify the max number of consecutive consonants and vowels in an e-mail address, using SAS SQL (proc sql). The output should look like the one below in columns Max of consecutive consonants and max of consecutive vowels (I listed…
Blake
  • 21
  • 3
1 2 3
…
52 53